Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

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

ToniMahonie
6 - Meteoroid

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

4 REPLIES 4
jrgo
14 - Magnetar
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
6 - Meteoroid

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
5 - Atom

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
5 - Atom

I figured it out,

 

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

 

Thank you

Labels