Convert String to Date Time Filed in Azure Data Bricks

Question

I have the following text string that represents a date time from an application .

2021-11-22 07:28:47 PM

I need to convert this to a date time to do a DATE ADD operation .

I have tried this many ways with no success and it gives me null in Azure Data Bricks .

select '2021-11-22 07:28:47 PM' as DateTime_String, 
    to_date('2021-11-22 07:28:47 PM', 'yyyy-M-dd HH:mm:ss:SS a') as Attempt_1  , 
    date_format(date ('2021-11-22 07:28:47 PM'), "yyyy-MM-dd HH:mm:ss:SS a") as Attempt_2,
    to_timestamp('2021-11-22 07:28:47 PM', 'yyyy-MM-dd HH:mm:ss.SSS a') as Attempt_3,
     to_timestamp('2021-11-22 07:28:47 PM', 'yyyy-MM-dd HH:mm:ss ') as Attempt_4

Answer 1

you can use the following approach to meet your requirement.

1. Convert your string to a unix timestamp (in seconds)
2. create datetime column in your desired format from the unix timestamp.

the code & output would be as follows:

select '2021-11-22 07:28:47 PM' as DateTime_String,unix_timestamp('2021-11-22 07:28:47 PM', 'yyyy-MM-dd hh:mm:ss aa') as unixtimestamp_value, from_unixtime(unix_timestamp('2021-11-22 07:28:47 PM', 'yyyy-MM-dd hh:mm:ss aa'),'yyyy-MM-dd HH:mm:ss')  as desired_format