How to combine date from one column and time from another

Question

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

• FROMDATEthat contains dates without time, for example. 08-01-2017 00:00:00
• FROMTIMEthat contains times without a date, for example. 01-01-1899 13:50:00

Now I want to select both inside the same column, for example. SELECT (<what ever>) as FROMDATETIME ...

Expected Result: 01-01-2017 13:50:00

But I can’t extract the date and time details and add them together like datetime.

They say in the form of meta the sql: select (date(FROMDATE) + time(FROMTIME)) as FROMDATETIME.

I tried a lot with TO_CHARand TO_DATE, but could not get the expected result.

Answer 1

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

Answer 2

char, :

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

... , ( ),

Answer 3

:

fromtime + (fromdate - trunc(fromtime))

fromdate - trunc(fromtime) "" ( trunc ) "". 43106 2017-01-08 1899-01-01. fromtime, 1899 43106 , 2017 .

, CTE , :

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

Answer 4

fromdate fromtime DATE, :

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;

VARCHAR,

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;