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

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'

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.