How to convert VARCHAR (AM/PM) to TIMESTAMP (24 h) in SQL (Teradata v17)

Question

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.

Answer 1

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')

Answer 2

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')

Answer 3

All you need is pad day and month in Teradata (as opposed to Oracle etc). m/d/y format has not been implemented.

select '8/3/2022 4:58:49 PM' date1,
to_timestamp(
  lpad(strtok(date1,'/',1),2,'0')||'/'||lpad(strtok(date1,'/',2),2,'0')||'/'||strtok(date1,'/',3)
  ,'mm/dd/yyyy hh24:mi:ss AM'
);