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

user_19344419 0
Viewed 30

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.

sql-timestamp teradata sql
3 Answers

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

user_27515730

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

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'
);
user_58129810
Related