Convert String to Date

[superjesse]

Hello all,

How do I convert 09222018 to a date? I can't get the string to date function to work because I think I have to have it in yyyy-mm-dd format. I tried different ways and cannot achieve this. I also tried the datetimeparse formula, but keep getting a null value.

DateTimeParse([Timestamp UI],'%mm-%dd-%yyyy')

Thanks in advance.

Jesse

[superjesse]

Hello all,

How do I convert 09222018 to a date? I can't get the string to date function to work because I think I have to have it in yyyy-mm-dd format. I tried different ways and cannot achieve this. I also tried the datetimeparse formula, but keep getting a null value.

DateTimeParse([Timestamp UI],'%mm-%dd-%yyyy')

Thanks in advance.

Jesse

[DavidxL · Accepted solution]

The format you provided was incorrect: see Specifiers https://help.alteryx.com/current/Reference/Functions.htm?Highlight=date

I attached a workflow as an example.

[superjesse]

This community is awesome!!!!!! Thank you so much!!

[AKnutson11]

You may also want to try Datetime tool under the Parse tools. It has similar functionality and is a bit more user friendly if all of your dates are in a clean format like your example. 

https://help.alteryx.com/current/DateTime.htm

[xiaoyan]

Hi all,

Can anyone guide me how to convert the format from 2019-02-26 to a date format like this: ddmmmyy (i.e.,26Feb19)?

I tried to use DateTimeParse "DateTimeParse([Incurred From Date],"%d,%b,%y")", but there's a warning message saying cannot convert "2019-04-22" to a date /time with format "],"%d,%b,%y": Expected separator , ],"%b,%y, got '19-04-22'.

I even tried to use the select tool to change the format to date first, but the result keep showing null.

[VigneshKS]

1. First use Date time tool from Parse and convert the Date field to a proper Date format

2. Drag formula tool and write the query as DateTimeFormat([Datefield],"%d-%b-%y")

3. Now again drag the formula tool and write the query as (Regex_Replace([Datefield],"[^a-zA-Z0-9]",''))

This method will give as what you requested. Hope it works for you

[VigneshKS]

h

[Mateusz_Palasz]

Hi guys

I am trying to convert string

04.01.2017 13:48:04

to datetime format by using the following formula

DateTimeParse([column_name],"%d%m%y%h%m%s")

but I got the error

ConvError: Formula (19): DATETIMEPARSE: Cannot convert "04.01.2017 13:48:04" to a date/time with format "%d%m%y%h%m%s": Expected a number for Month: '.01.2017 13:48:04'

could you please help me to solve this?

Thank you, Mateusz

[Jean-Balteryx]

Hi @Mateusz_Palasz ,

Try using %M for month instead of %m. It should work.

Cheers,

Jean-Baptiste

[jessle]

Hi Mateusz_Palasz,

You just need to make sure the format you're providing is exact (i.e. it also contains the '.' and ':' delimiters, and a space between the date and time parts) and that you're using the correct specifiers (the hour-minute-second letters need to be capitalised). You can check your specifiers here: https://help.alteryx.com/current/designer/datetime-functions

DateTimeParse([OriginalDate],'%d.%m.%y %H:%M:%S')



(If you want the time included in the output as well, just change the data type to 'DateTime').

You could also use the DateTime tool if you want a friendlier interface:



Hope this helps!