Create Timestamp from Date and Time

Database
Enthusiast

Create Timestamp from Date and Time

Dear All,
I wonder what is the best way how to provide a Timestamp column in view based on date and time column from table. So far we have two options, both seems me still quite complicated. Thanks for tips or confirming of no way :)

SELECT CAST(CAST(CURRENT_DATE AS FORMAT 'YYYY-MM-DD') || ' ' || CAST(CAST(CURRENT_TIME AS FORMAT 'HH:MI:SS') AS CHAR(8)) AS TIMESTAMP(0));

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Thanks

petr
2 REPLIES
Enthusiast

Re: Create Timestamp from Date and Time

When I run the following the date portion of the timestamp is always the previous day.

SELECT CAST(CURRENT_DATE AS TIMESTAMP(0)) + ((CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0));

Anybody know why this is occuring?

Re: Create Timestamp from Date and Time

hey Petr,

just found a solution:

SELECT

CAST((CURRENT_DATE + .5) AS TIMESTAMP(0)) + (( CURRENT_TIME - TIME '00:00:00') HOUR TO SECOND(0))

;