I've tried multiple solutions, but I keep getting errors. I need to create a new column casting VARCHAR to TIMESTAMP that includes AM, PM or -ideally- changes it to 24 hrs format.

VARCHAR format (Start_Date column): 8/3/2022 4:58:49 PM

I found the below solution is some other post, but I'm getting error: 'Format code appears twice'

SELECT itab.*, 
TO_TIMESTAMP(Start_Date, 'MM/DD/YYYY HH:MM:SS AM') AS start_TS
FROM db.info_table itab

Please advise.

CodePudding user response：

You have two problems.

1. MI is the format for minutes, MM is for months (you have it twice, this is why you are getting that error).
2. Your date/time string has single digit values for month, day, etc. You can use a pretty simple regex for that.

select to_timestamp(regexp_replace('8/3/2022 4:58:49 PM', '\b([0-9])\b', '0\1'), 'MM/DD/YYYY HH:mi:SS AM')

CodePudding user response：

TO_TIMESTAMP returns a TIMESTAMP(6). If you don't want microseconds you can specify the precision using

CAST(RegExp_Replace(start_date, '\b([0-9])\b', '0\1') AS timestamp(0) FORMAT 'MM/DD/YYYYbHH:Mi:SSbT')