OraFAQ Forum: Forms » sql to get difference between 2 dates (merged)

Home » Developer & Programmer » Forms » sql to get difference between 2 dates (merged)

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

sql to get difference between 2 dates (merged) [message #678726]

Sun, 05 January 2020 06:23

compuscience
Messages: 97
Registered: September 2012

Member

hello
i made the following sql to get difference between 2 dates
like hiredate and current date for an employee

:datte:=round(mod(months_between(sysdate,:hiredate),1)*30)||' D '||
trunc(mod(months_between(sysdate,:hiredate),1)*12)||' M '||
trunc(months_between(sysdate,:hiredate)/12)||' Y ';

this gives Y M D that's ok
but i need to add 2Y 3M 0D to the above code
how can i do that to affect on Y M D
i.e this status is wrong 10Y 14M 9D it must be 11Y 2M 9D
How can i do that???

Report message to a moderator

Re: sql to get difference between 2 dates (merged) [message #678728 is a reply to message #678726]

Sun, 05 January 2020 08:06

Michel Cadot
Messages: 68501
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

And feedback to people who help you.

Report message to a moderator

Re: sql to get difference between 2 dates (merged) [message #678731 is a reply to message #678728]

Sun, 05 January 2020 12:46

Littlefoot
Messages: 21782
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Quote:

this gives Y M D that's ok

Well, yes - it is OK if you want to stop here, but not that OK if you want to do something more - in that case, do the date arithmetic before formatting it to be nicely displayed to end users.

This is what you have now (presume that hiredate = 01-01-2000):

SQL> select months_between(sysdate, date '2000-01-01') monbet from dual;

    MONBET
----------
240,155461

SQL>

You'd want to add 2 years and 3 months; use **add_months** function - that means that you'll have to convert years to months (which is simple; multiply number of years by 12 (months in a year)). Added to 240.16 months, that would be [2 years * 12 months + 3 months] = [24 months + 3 months] = [27 months] which becomes [240.16 + 27 = 267.16], i.e.

SQL> select months_between(add_months(sysdate, 2*12 + 3), date '2000-01-01') add_monbet from dual;

ADD_MONBET
----------
267,155573

SQL>

Now do your Y-M-D calculation/formatting.

Report message to a moderator

Re: sql to get difference between 2 dates (merged) [message #678761 is a reply to message #678731]

Tue, 07 January 2020 16:28

compuscience
Messages: 97
Registered: September 2012

Member

Thanks for you reply
but it didn't give me accurte Year Month Day after adding my 2 years and 3 months

could you give me full equation to get at the final that i have the following
without adding 20 Years 0 Months 7 Days
with adding 22 Years 3 Months 7 Days

thank you very much

[EDITED by LF: removed unnecessary quote of the whole previous message]

[Updated on: Sat, 11 January 2020 13:26] by Moderator

Report message to a moderator

Re: sql to get difference between 2 dates (merged) [message #678762 is a reply to message #678761]

Tue, 07 January 2020 16:48

buggleboy007
Messages: 282
Registered: November 2010
Location: Canada

Senior Member

Quote:

Thanks for you reply
but it didn't give me accurte Year Month Day after adding my 2 years and 3 months

could you give me full equation to get at the final that i have the following
without adding 20 Years 0 Months 7 Days
with adding 22 Years 3 Months 7 Days

thank you very much

Try on your own. Littlefoot has given you an idea/example. He has used Y2K as a starting example. Use the year that you want and extract the results.

Report message to a moderator