Home » Developer & Programmer » Forms » sql to get difference between 2 dates (merged)
sql to get difference between 2 dates (merged) [message #678726] Sun, 05 January 2020 06:23 Go to next message
compuscience
Messages: 62
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???
Re: sql to get difference between 2 dates (merged) [message #678728 is a reply to message #678726] Sun, 05 January 2020 08:06 Go to previous messageGo to next message
Michel Cadot
Messages: 66934
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.

Re: sql to get difference between 2 dates (merged) [message #678731 is a reply to message #678728] Sun, 05 January 2020 12:46 Go to previous messageGo to next message
Littlefoot
Messages: 21548
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.
Re: sql to get difference between 2 dates (merged) [message #678761 is a reply to message #678731] Tue, 07 January 2020 16:28 Go to previous messageGo to next message
compuscience
Messages: 62
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 Go to previous messageGo to next message
buggleboy007
Messages: 241
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.
Re: sql to get difference between 2 dates (merged) [message #678763 is a reply to message #678762] Wed, 08 January 2020 03:09 Go to previous messageGo to next message
compuscience
Messages: 62
Registered: September 2012
Member
i do it many time with no result
so i need help from orqfaq
Re: sql to get difference between 2 dates (merged) [message #678764 is a reply to message #678763] Wed, 08 January 2020 03:27 Go to previous message
cookiemonster
Messages: 13864
Registered: September 2008
Location: Rainy Manchester
Senior Member
So show us what you tried, what the result was and what result you expect.
Previous Topic: Looping Problem
Next Topic: Forms 12c: Internet explorer does nothing
Goto Forum:
  


Current Time: Sat Apr 04 23:09:38 CDT 2020