OraFAQ Forum: SQL & PL/SQL » Subtracting dates

Home » SQL & PL/SQL » SQL & PL/SQL » Subtracting dates (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Subtracting dates [message #649568]

Thu, 31 March 2016 03:39

javon13
Messages: 17
Registered: October 2015

Junior Member

Hello everyone,

I am in need to subtract two dates and get a number of days.
OK, that's easy enough. But I realized this:

BEGIN
  DBMS_OUTPUT.put_line (to_date('2010-10-05','YYYY-MM-DD') - SYSDATE);
END;
/

This gives me the result:
-2004,433043981481481481481481481481481481

As I need exact number of days I'd like to get rid of numbers behind decimal point.
And this is the question for the forum: Is it safe enough to use it like this?:

BEGIN
  DBMS_OUTPUT.put_line (to_date('2010-10-05','YYYY-MM-DD') - to_date(SYSDATE));
END;
/

And the result is:
-2004

In real life I have two dates (DD.MM.YY) from the table. Something like:

declare
 dateone DATE;
 datetwo DATE;
 rs_days NUMBER;
begin
 dateone := PKG.GETPARAM('START_DATE');
 datetwo := ADD_MONTHS(dateone,4);

 rs_days := to_date(dateone) - to_date(datetwo);
 dbms_output.put_line('Days: ' || rs_days);
end;
/

Thanks again in advance for your time.

[Updated on: Thu, 31 March 2016 03:40]

Report message to a moderator

Re: Subtracting dates [message #649570 is a reply to message #649568]

Thu, 31 March 2016 03:51

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

This is a bug waiting to bite,

 DBMS_OUTPUT.put_line (to_date('2010-10-05','YYYY-MM-DD') - to_date(SYSDATE));

if you want to strip off the hours/minutes/seconds, use the TRUNC function.

Report message to a moderator

Re: Subtracting dates [message #649571 is a reply to message #649570]

Thu, 31 March 2016 03:57

javon13
Messages: 17
Registered: October 2015

Junior Member

Alright. But if the result would be for instance:
87.95
Then trunc() makes it 87 but it wouldn't probably be as right as I need.

begin
dbms_output.put_line(trunc(87.95));
end;
/

Result: 87

Report message to a moderator

Re: Subtracting dates [message #649572 is a reply to message #649571]

Thu, 31 March 2016 04:01

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You can always use round, floor or ceil on the resulting number.

Report message to a moderator

Re: Subtracting dates [message #649573 is a reply to message #649571]

Thu, 31 March 2016 04:02

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

orclz> select sysdate,trunc(sysdate) from dual;

SYSDATE             TRUNC(SYSDATE)
------------------- -------------------
2016-03-31:10:02:09 2016-03-31:00:00:00

orclz>

Report message to a moderator

Re: Subtracting dates [message #649584 is a reply to message #649573]

Thu, 31 March 2016 06:42

javon13
Messages: 17
Registered: October 2015

Junior Member

@John Watson: OK, I see what you mean. Thanks.

Report message to a moderator

Re: Subtracting dates [message #649586 is a reply to message #649568]

Thu, 31 March 2016 06:57

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

Code: [Select all] [Show/ hide]

BEGIN
  DBMS_OUTPUT.put_line (to_date('2010-10-05','YYYY-MM-DD') - to_date(SYSDATE));
END;
/

NO!
Do not apply to_date to something (like sysdate) that is already a date.
The input of to_date is a character string. If you try to give to_date a DATE, then it has to do an implicit conversion of that DATE to a character string, just to convert it back to a DATE. And that implicit conversion may very well not be what you think. (Besides the fact that it simply makes no sense to try to convert a DATE to a DATE).

Report message to a moderator

Re: Subtracting dates [message #649599 is a reply to message #649568]

Thu, 31 March 2016 12:59

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

-- package for demonstration:

SCOTT@orcl> create or replace package pkg as
  2    function getparam (p_string in varchar2) return date;
  3  end pkg;
  4  /

Package created.

SCOTT@orcl> create or replace package body pkg as
  2    function getparam (p_string in varchar2) return date is
  3    begin
  4  	 return sysdate;
  5    end getparam;
  6  end pkg;
  7  /

Package body created.

-- correction of your code, eliminating to_date where inappropriate and adding round:

SCOTT@orcl> declare
  2   dateone DATE;
  3   datetwo DATE;
  4   rs_days NUMBER;
  5  begin
  6   dateone := PKG.GETPARAM('START_DATE');
  7   datetwo := ADD_MONTHS(dateone,4);
  8   rs_days := round (dateone - datetwo);
  9   dbms_output.put_line('Days: ' || rs_days);
 10  end;
 11  /
Days: -122

PL/SQL procedure successfully completed.

-- simplification of above code:

SCOTT@orcl> begin
  2   dbms_output.put_line
  3  	('Days: ' || round (PKG.GETPARAM('START_DATE') - add_months (PKG.GETPARAM('START_DATE'), 4), 0));
  4  end;
  5  /
Days: -122

PL/SQL procedure successfully completed.

Report message to a moderator

Re: Subtracting dates [message #649633 is a reply to message #649599]

Fri, 01 April 2016 09:36

Bill B
Messages: 1971
Registered: December 2004

Senior Member

Or an easy way is

BEGIN
  DBMS_OUTPUT.put_line (round(to_date('2010-10-05','YYYY-MM-DD') - SYSDATE));
END;
/

Report message to a moderator

Re: Subtracting dates [message #649649 is a reply to message #649633]

Fri, 01 April 2016 14:26

Barbara Boehmer
Messages: 9088
Registered: November 2002
Location: California, USA

Senior Member

Bill,

I don't have his packaged function code and don't know what it does, presumably something more complex than just returning sysdate. I provided a packaged function that just returns sysdate, in order to have a packaged function to call to demonstrate code similar to what he said he has. Obviously, if it just returns sysdate, then your code would be simpler.

Barbara

Report message to a moderator

Previous Topic:	Oracle SQL - Parameter in where condition to search two values from one
Next Topic:	plsql logic (merged 3)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 09:54:44 CDT 2024