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))
select CAST(CAST(CAST(CAST(current_timestamp as format 'MM/DD/YYYYb HH:MI') as CHAR(16)) || ':00' as VARCHAR(19)) as timestamp(0))
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)
Thanks Dieter ! Is there any way to REMOVE the second but still keep it in a valid date or timestamp format ?
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))