Date Conversions

[Cooper_yonk]

Hi peeps.

Does anyone know how I would turn a date 20/02/2016 which has come into alteryx as a text field, into a date?

[JohnJPS]

Please check the answer here (http://community.alteryx.com/t5/Data-Preparation-Blending/Date-Conversion/m-p/13964#U13964) to see if it solves your problem.

Thanks!

[JoeM]

In the parse tab, find the DateTime tool. This tool will be able to convert your string format to a date.

.

[AndrewW]

Use the DateTime tool in a Parse area

[s_pichaipillai · Accepted solution]

@Cooper_yonk

here are few ways you can follow

1. Using DateTime Tool Under Parse Menu

new user often confused with this tool how to use this format options

In you example , the text format is , DD/MM/YYYY so in the Datetime pare tool you need to select the incoming string format 

see the image below

2. Using formula tool :

you can also use formula tool to create a new Date type column and using the expression bellow

DateTimeParse([Date],'%d/%m/%Y')

PFA sample workflow for your reference

[Cooper_yonk]

 Hi again

I thought it had worked but it hadnt.  I've changed the string to Date but my output tde file now says [Null]...whys it not making it a date?.  The format of the date going in is dd/mm/yyyy and I want the output to be dd/mm/yyyy but as a date not a string.

Can anyone help please?

[PaulT]

Greetings!

The issue you are running in to has to do with date formatting. Changing a field with dates formatted as mm/dd/yyyy to a Date field using the Select tool will yield null values. Alteryx handles Dates much as databases do, which is to say they expect the standardized format of yyyy-mm-dd hh:mm:ss for Date/Time and yyyy-mm-dd for date only. In this format Alteryx (and many databases as well) are able to track time and do calculations against those dates.  My advice would be to use the Date/Time tool as suggested above to get the yyyy-mm-dd field, do your calculations with that field, and if you prefer the other date format, at the end of your workflow use the Date/Time tool again this time selecting "Convert From Date/Time Field to Formatted String" and selecting the appropriate format you would like your date to come out as. Keep in mind, the final output will be mm/dd/yyyy (or whatever you select), and it will be a String field not a date.

Hope this helps!

Paul

[Fz]

Hello, 

I'am using DateTime to convert Date, when I run my workflow, the field is null. 

I join a screenshot of my woorkflow to clear up the error. 

Thank you .

[PaulT]

Greetings @Fz,

I'm curious about why you're using a DateTimeNow tool then using a DateTime tool in tandem. You can just select the date time format you want in the DateTimeNow tool from the drop down. This will allow you to get a valid "DateTime" Field right out of the first tool.

The mistake you have made in your selection in the DateTime tool is that you have selected how you want the data to appear upon output, instead of how the data currently looks. The purpose of the selection box with all the different formats is for you to select the format that your String data currently looks like. This tells the DateTime tool what to expect. The default output for the DateTime tool is going to be yyyy-mm-dd hh:mm:ss.

Thanks,

Paul

[alteryx_user]

Dear JoeM,

When I try to apply your solution, the first row of each set of date turns out as expected. But the second row turns [null]. 

Appreciate your help.

Date	Result	Expectation
4/30/12	2012-04-30	2012-04-30
4/30/12	[null]	2012-04-30
1/1/19	2019-01-01	2019-01-01
05/27/19	2019-05-27	2019-05-27
05/27/19	[null]	2019-05-27
05/27/19	[null]	2019-05-27