String to Date Format Conversion resulting in NULL - Databricks

Question

I have date saved in the below format :

+-------------------------+
| timex    (varchar)      |
+-------------------------+
| 20200303                |
+-------------------------+

I want the output/convert the value into date /time format in Databricks SQL.

Should be :

+-------------------------+
| timex                   |
+-------------------------+
| 2020-03-03 00:00:00.000 |
+-------------------------+

Tried :

1. select to_timestamp(timex) --> results in 1970-xx-xx
2. select to_timestamp(timex, 'yyyy/MM/dd HH:mm:ss.ssssss') --> says datatype in argument 1 should be in string or date
3. select to_timestamp(CAST(timex AS STRING), 'yyyy/MM/dd HH:mm:ss.ssssss') --> NULL
4. select to_date(timex) --> NULL

Equivalent in SQL - convert(datetime,cast(timex as nvarchar),103)

If I use cast(timex as string) as xx --> it works But, i I use to_timestamp(cast(timex as string), 'yyyyMMdd') as t --> it gives null.

I tried to format the string also to_timestamp(format_string('%8d',cast(timex as string)), 'yyyyMMdd') --> getting exception : IllegalFormatConversionException

Answer 1

You need to convert to timestamp using to_timestamp and then to the desired format using date_format:

select date_format(to_timestamp(format_string('%8d', timex), 'yyyyMMdd'), 'yyyy-MM-dd HH:mm:ss.SSS')
from mytable;