CODEWITHSUNDEEP
sqloracle-databasedate
BendEg
BendEg

Reputation: 21128

How to combine date from one column and time from another

Currently I'm trying to select a datetime column from two date columns. I'm having the following situation:

  1. FROMDATE column which contains dates without time, e.g. 08-01-2017 00:00:00
  2. FROMTIME column which contains times without a date, e.g. 01-01-1899 13:50:00

Now I want to select both within one column, e.g. SELECT (<what ever>) as FROMDATETIME ...

Expected result: 08-01-2017 13:50:00

But I'm not able to extract the date and time parts and add them together as a datetime.

Spoken in a kind of meta sql: select (date(FROMDATE) + time(FROMTIME)) as FROMDATETIME.

I've tried a lot with TO_CHAR and TO_DATE but was not able to get the expecting result.

Upvotes: 6

Views: 5213

Answers (4)

Aleksej
Aleksej

Reputation: 22969

A way could be casting everything to char and then using the concatenatoin of values:

select to_date(to_char(FROMDATE, 'dd-mm-yyyy') || to_char(FROMTIME, 'hh24:mi:ss'), 'dd-mm-yyyyhh24:mi:ss')
from test

...but what I wanted to do (and I was unable to) is in Alex Poole's answer

Upvotes: 4

Alex Poole
Alex Poole

Reputation: 191560

You could also use date arithmetic:

fromtime + (fromdate - trunc(fromtime))

The fromdate - trunc(fromtime) part gets the number of whole days between the nominal date in the 'time' column (with trunc to zero the time in that) and the actual date in the 'date' column. In your example that is 43106 days between 2017-01-08 and 1899-01-01. That number of days can then be added to the fromtime, which takes its date in 1899 up 43106 days to match the 2017 date.

Quick demo, using a CTE to generate the two example values, and showing the number of days between the dates as well as the result you want:

with t (fromdate, fromtime) as (
  select date '2017-01-08', cast (timestamp '1899-01-01 13:50:00' as date)
  from dual
)
select fromdate - trunc(fromtime) as days,
  fromtime + (fromdate - trunc(fromtime)) as fromdatetime,
  to_char(fromtime + (fromdate - trunc(fromtime)), 'DD-MM-YYYY HH24:MI:SS') as formatted
from t;

      DAYS FROMDATETIME        FORMATTED          
---------- ------------------- -------------------
     43106 2017-01-08 13:50:00 08-01-2017 13:50:00

Upvotes: 3

Anthony Raymond
Anthony Raymond

Reputation: 7872

If your fromdate and fromtime columns are DATE type, you can do the following:

SELECT
    TO_DATE(
      TO_CHAR(fromdate, 'DD-MM-YYYY') || ' ' || TO_CHAR(fromtime, 'HH24:MI:SS'),
      'DD-MM-YYYY HH24:MI:SS'
    ) AS fromdatetime
FROM my_table;

If your columns are VARCHAR, you have to do the following

SELECT
    TO_DATE(
      TO_CHAR(TO_DATE(fromdate, 'DD-MM-YYYY HH24:MI:SS'), 'DD-MM-YYYY') || ' ' || TO_CHAR(TO_DATE(fromtime, 'DD-MM-YYYY HH24:MI:SS'), 'HH24:MI:SS'),
      'DD-MM-YYYY HH24:MI:SS'
    ) AS fromdatetime
FROM my_table;

Upvotes: 2

sagi
sagi

Reputation: 40491

Here you go:

SELECT TO_DATE(TO_CHAR(t.fromdate,'dd-mm-yyyy') ||
               ' ' ||
               TO_CHAR(t.fromtime,'hh24:mi:ss'),'dd-mm-yyyy hh24:mi:ss') as full_date_col 
FROM YourTable t

Upvotes: 5

Related Questions