Question: How to combine date from one column and time from another

Question

How to combine date from one column and time from another

Answers 4
Added at 2016-12-29 11:12
Tags
Question

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.

Answers to

How to combine date from one column and time from another

nr: #1 dodano: 2016-12-29 11:12

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
nr: #2 dodano: 2016-12-29 11:12

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

nr: #3 dodano: 2016-12-29 11:12

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;
nr: #4 dodano: 2016-12-29 11:12

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
Source Show
◀ Wstecz