String to date conversion 'dd.mm.yyyy hh:mm:ss'

[ToniMahonie]

Hi community,

I am new to alteryx and facing since two days the challenge of formating a German time format from a csv-file to a date format.

Source format of the field [MyDate]: 'dd.mm.yyyy hh:mm:ss' 

Some entries in [MyDate] have a short format: 'dd.mm.yyyy'

I have tried following formula: DateTimeParse(Left([MyDate],10), "DD%.MM%.YYYY%")

and got the error:

"ConvError: Formula (6): DATETIMEPARSE: Cannot convert "01.09.2017" to a date/time with format "DD%.MM%.YYYY%": Expected separator 'DD%.MM%.YYYY%', got: '01.09.2017'"

Any ideas how I can solve this? Step by step explanation highly appreciated J

Thanks

[jrgo · Accepted solution]

The proper format specifiers would be %d.%m.%Y

https://help.alteryx.com/11.5/index.htm#Reference/DateTimeFunctions.htm%3FTocPath%3DReference%7C____...

All the various Date part specifiers are listed in here.

[ToniMahonie]

Thanks jrgo,

it works fine know!

I found also a workaround: 

 1. Step: Seperating the date field with prase RegEx using this Expression: (\d+).(\d+).(\d+) (\d+):(\d+):(\d+)

 2. Step: Using this formula to build the date: tostring([Year]+"-"+[Month]+"-"+[Day])

But your way is much easier :)

Thank you!

[kareenshehab]

Hey there, can you please explain exactly how to do this? Are you using the datetime tool, pressing custom then writing the formula DateTimeParse(Left([P00 Date SUIM],10), "%d.%m.%Y")? I'm trying to convert dates like 18.05.2018 to 5/18/2018, but it is not working.

[kareenshehab]

I figured it out,

Using the 'formula' tool I wrote down DateTimeParse([insert column name], "%d.%m.%Y") and it worked. 

Thank you