Amanda R. Amanda R. - 4 months ago 6
SQL Question

Merge date and time columns

Currently I have 2 columns one that holds dates and the other holds the time (24hr text version).

Example:

a_dt a_time_text
01-Jan-15 0224
31-Mar-15 0317
15-Sep-15 2010


How do I create a new column that hold the date and time, like this:

a_dt_tm
01-Jan-15 02:24
31-Mar-15 03:17
15-Sep-15 20:10


Or any other way so that I can find the difference in date/time between another variable.

Answer

You can do this:

to_date
   ( to_char (a_dt,'YYYYMMDD')
        || a_time_text,
     'YYYYMMDDHH24MI'
   )

i.e. first convert the date column to a string - to_char (a_dt,'YYYYMMDD'), then append the time string, then convert the resulting string back to a date.

Comments