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?

  • If both are dates then Bar doesn't hold a time value. there's no where to put it. – user1443098 Oct 18 '19 at 20:23
  • Do you not want to use varchar in any step or just the Foobar variable? – Rogerlr Oct 18 '19 at 20:31
  • 2
    user1443098 - Oracle DATE (see tag) always contains a time component. Other RDBMS have separate DATE, TIME, and DATETIME data types. Oracle does not. – Michael Kutz Oct 18 '19 at 21:14

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

You can use DATEPART to pull the hour/min/sec from BAR and do the same to pull day/month/year from foo, them use DATEFROMPARTS to reassemble.

Pity SQL Server doesn't support the ANSI INTERVAL type, which would make this super simple.

  • 1
    There is no DATEPART in Oracle – user153556 Oct 20 '19 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.