databricks-sqlto-timestamp

Databricks sql: How to convert string to timestamp when string contains extra characters

I have a database column which has a timestamp in string format. The problem is that the string has a "T" dividing the day and time sections. The strings look like this:

20240724T083000+0100
20240724T083000+0100
20240724T083000+0100
20240724T083000+0100

How do I use the to_timestamp function to convert this string to timestamp?

When I pass 'yyyy-MM-dd"T"HHmmssx' or 'yyyy-MM-ddTHHmmssx' as formats, it throws an error.

Solution

  • I figured it out. I needed to put the T in quotes and escape the quote character.

    The format string should be 'yyyyMMdd\'T\'HHmmssx' or to_timestamp('20240724T083000', 'yyyyMMdd\'T\'HHmmss') for my example.