How to combine date and time column to form a timestamp field.

The most efficient way to combine a date and a time is to cast the date to a timestamp and add the time. But you can’t simply add a time, because TIME is a point in time and not a duration. So you have to transform it into an interval:

cast(Date_column as TimeStamp(6))
+ ((Time_column - time '00:00:00') hour to second(6)) 

Example:

  SELECT CAST(DATE'1999-12-30' AS TIMESTAMP(6))
+ ((TIME '12:12:21' - TIME '00:00:00') HOUR TO SECOND(6))

gives:

 (1999-12-30+(12:12:21 - 00:00:00) HOUR TO SECOND)
 1999-12-30 12:12:21.000000
 

We can also confirm the data type of the value returned by using TYPE

  SELECT TYPE(CAST(DATE'1999-12-30' AS TIMESTAMP(6))
+ ((TIME '12:12:21' - TIME '00:00:00') HOUR TO SECOND(6)))

gives

 Type((1999-12-30+(12:12:21 - 00:00:00) HOUR TO SECOND))
 TIMESTAMP(6)

Main point to be noted here is that we cant simply add TIME.

First we need to convert it to interval and then only add to timestamp.

(courtesy-Teradata forum)