How to convert a string into timestamp(0) in Teradata?

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

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

2 Answers

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

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'