Transform string to date data type

[Solonglolli]

Hi there,

I'm a newbie and looking to convert ther records in a date field where the date is expressed in the format of '01-Jan-19' into the alteryx date format (yyyy-mm-dd). I have read through and tried a number of suggestions within this section and I still get the same error when using the DateTime parse tool. I have set the input data type to String and I have specified the incoming string field in a Custom format using different parameters including dd-Mon.-yy yet I always get the same error:

Would anyone have an idea what is going wrong? Once successfully converted I'd like to assign a new column called quarter based on the dates.

Also is it better to use a syntax or the parse tool for converting dates to datetime?

Thanks!

[RolandSchubert]

Hi @Solonglolli ,

you can use the formula:

DateTimeParse([Field1], '%d-%b-%y') 

Let me know if it works for you.

Best,

Roland

[Solonglolli · Accepted solution]

Thanks for the help and suggestion. An error came back first to say that the formula resulted in a string but the output field was set to numeric so i changed the output field to string and it was perfect then.

What is the default input and output date type for the datetime formula? Would the formula work with any kind of input data type or does it only work for string to be converted to string?

[RolandSchubert · Accepted solution]

The input data type has to be a string (or to be converted to a string using ToString function), the output data type is always Date, DateTime or Time.
If you need a different output data type, you can use DateTimeFormat([DateField], 'Format') , (Format is the specification) returning a string.