Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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,...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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's avatar
mahoneypat
Microsoft Employee
5 years ago

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Worked perfectly, thank you very much 🙂