How to Convert a String into Timestamp(0) in Teradata?

Question

I keep getting either "Unknown character F in format string"

select to_timestamp('06/05/2022 03:51:00','MM/DD/YYYY HH:MI:SS.FF0');

But FF6 format works.

Or "Datetime field overflow" if I do this (I presume this creates a timestamp(6) by default).

insert into table_with_timestamp_0 
select to_timestamp('06/05/2022 03:51:00');

Or

insert into table_with_timestamp_0 
select cast( to_timestamp('06/05/2022 03:51:00') as timestamp(0) );

Answer 1

This is one way to do it:

select cast(cast(
    to_timestamp('06/05/2022 03:51:00','MM/DD/YYYY HH:MI:SS') 
  as varchar(19)) as timestamp(0));

Answer 2

TO_TIMESTAMP always returns a TIMESTAMP(6), which fails when you try to truncate the precision.

Use Teradata-style FORMAT:

Cast('06/05/2022 03:51:00' AS TIMESTAMP(0) FORMAT 'MM/DD/YYYYbHH:MI:SS')

But if you want to hard-code a timestamp simply use a Standard SQL Timestamp Literal which always assumes YYYY-MM-DD format:

timestamp '2022-06-05 03:51:00'

2 Answers