Merge date and time columns

Question

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 1

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.