How to add time from a Date value to another without a varchar conversion

datedatetimeoraclequery

Let's say I have 2 variables in a Stored Procedure, both are Dates

Foo contains '13/10/2019 00:00:00'

Bar contains '01/10/1914 16:33:11'

I want to add Bar's time to Foo's date, so that I get a Foobar variable containing 13/10/2019 16:33:11

Is there a way to achieve this without converting any of them into a varchar?

Best Answer

Simple addition and subtraction.

Although the code is SQL code, the math works the same in PL/SQL.

This code:

alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
with data as (
    select TO_date( '13/10/2019 00:00:00', 'DD/MM/YYYY HH24:MI:SS') as foo
         , to_date( '01/10/1914 16:33:11', 'DD/MM/YYYY HH24:MI:SS') as bar from dual
)
select foo, foo + (bar - trunc(bar)) foo_modified, bar from data

Produces this desired results:

FOO                 FOO_MODIFIED        BAR                
------------------- ------------------- -------------------
2019-10-13 00:00:00 2019-10-13 16:33:11 1914-10-01 16:33:11
Related Question