How to store a timestamp value with seconds constant at :00

Database

How to store a timestamp value with seconds constant at :00

All,

I'm trying to filter the DBQL and noticed that timestamp does not support a format WITHOUT seconds (ie:  'MM/DD/YYYY HH:MI).  Therefore, I found a workaround appending :00 as a constant for the second:

select CAST(CAST(CAST(current_timestamp as format 'MM/DD/YYYYb HH:MI') as CHAR(16)) || ':00' as VARCHAR(19))

I would like to cast this back to a timestamp with the :00 second set as a constant:

select CAST(CAST(CAST(CAST(current_timestamp as format 'MM/DD/YYYYb HH:MI') as CHAR(16)) || ':00' as VARCHAR(19)) as timestamp(0))

but get the following error:

 [Teradata Database] [TeraJDBC 15.10.00.14] [Error 6760] [SQLState HY000] Invalid timestamp. 

Any ideas how I can work around this and get my format back to a timestamp  ??

Tags (1)
3 REPLIES
Junior Contributor

Re: How to store a timestamp value with seconds constant at :00

The default format for a timestamp is not MM/DD/YYYY:

CAST(CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'yyyy-mm-ddbHH:MI') AS CHAR(16)) || ':00' AS TIMESTAMP(0))

But you can simply subtract seconds:

CURRENT_TIMESTAMP(2) - (EXTRACT(SECOND FROM CURRENT_TIMESTAMP(2)) * INTERVAL '1' SECOND)

Re: How to store a timestamp value with seconds constant at :00

Thanks Dieter !   Is there any way to REMOVE the second but still keep it in a valid date or timestamp format ?   

Junior Contributor

Re: How to store a timestamp value with seconds constant at :00

A timestamp always includes seconds, you can only truncate to the minute (so it's always :00).

Or you cast it to a varchar and apply a display format to omit the seconds:

CAST(CAST(CURRENT_TIMESTAMP AS FORMAT 'yyyy-mm-ddbHH:MI') AS CHAR(16))