Converting date time in text format to date time format

[Anonymous]

The source data I'm trying to convert is in text format as "20201201 163045". I'm using to_timestamp and returning a value "12/01/2020 4:30:45 PM". 

This is SQL I'm using:

to_timestamp(txndatetime, 'yyyy-mm-dd HH24:mi:ss') As EventDate

How do I get this to return the date time in 24 hour format?

[mahoneypat · Accepted solution]

In your original text column (with 20201201 163045), you can do a Replace Values step and replace the space with a "T".  Then you can add a custom column with this formula to get your result.

= DateTime.ToText(DateTime.FromText([DateTimeText]), "MM/dd/yy HH:mm:ss")

Or (recommended) just use

= DateTime.FromText([DateTimeText])

And then use a format string after you load the query on that column to display it with that format (in quotes above).  That way it will stay as a DateTime instead of text.

Regards,

Pat

[crwinchester]

@mahoneypat , how would you convert text to datetime if the text is written, "01/23/2024 1:23 PM PST?" The above formulae doesn't work in this case and the timezone would be important. Thanks.

[Anonymous]

Worked perfectly, thank you very much 🙂