Forum Discussion
Converting date time in text format to date time format
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?
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
3 Replies
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
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.
