Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two dates (Oracle 10g, Win XP)
Difference between two dates [message #330212] Fri, 27 June 2008 23:52 Go to next message
madhuri_madhav
Messages: 8
Registered: May 2008
Location: Hyderabad
Junior Member
Hi friends,

How can I calculate the difference between two particular dates. I need the difference in Years, Months & Days.

For example:
Two dates are like

1) 20-JUL-2008
2) 10-MAY-2006

Result is like : 2 years, 2 months, 10 days.
Re: Difference between two dates [message #330214 is a reply to message #330212] Sat, 28 June 2008 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I put "difference in Years, Months & Days" in the search field you'll find in the menu bar and find:
- first line, your post
- second line, this
http://www.orafaq.com/forum/m/320585/102589/?srch=difference+in+Years%2C+Months+%26+Days#msg_320585

Please just search a little bit BEFORE posting.

Regards
Michel
Re: Difference between two dates [message #330215 is a reply to message #330212] Sat, 28 June 2008 00:29 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

try this query
select extract(year from sysdate)-extract(year from to_date('01-jan-2008'))year,
extract(month from sysdate)-extract(month from to_date('01-jan-2008')),
extract(day from sysdate)-extract(day from to_date('01-jan-2008')) day 
from dual

kanish

[Updated on: Sat, 28 June 2008 00:41] by Moderator

Report message to a moderator

Re: Difference between two dates [message #330219 is a reply to message #330215] Sat, 28 June 2008 00:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please keep your lines in 80 characters.
Use "Preview" button to verify.

What happens in your query if month or day of first date is greater than second one, for instance, 20-JUL-2006 and 10-MAY-2008?

Regards
Michel

[Updated on: Sat, 28 June 2008 00:45]

Report message to a moderator

Re: Difference between two dates [message #330221 is a reply to message #330212] Sat, 28 June 2008 00:47 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

we can use abs() in that case


kanish

[Updated on: Sat, 28 June 2008 00:47]

Report message to a moderator

Re: Difference between two dates [message #330222 is a reply to message #330221] Sat, 28 June 2008 00:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
mm_kanish05 wrote on Fri, 27 June 2008 22:47
we can use abs() in that case


kanish

Are you incapable or unwilling to test yourself?
Re: Difference between two dates [message #330224 is a reply to message #330221] Sat, 28 June 2008 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
mm_kanish05 wrote on Sat, 28 June 2008 07:47
we can use abs() in that case


kanish

No, try it, replace sysdate by the second date I gave and '01-jan-2008' by the first one, the result is wrong with or without abs (above all with).

Regards
Michel

Re: Difference between two dates [message #330228 is a reply to message #330212] Sat, 28 June 2008 01:26 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

As per requirement from madhuri_madhav

Quote:
1) 20-JUL-2008
2) 10-MAY-2006

Result is like : 2 years, 2 months, 10 days
.

if feel it is correct.


kanish

[Updated on: Sat, 28 June 2008 01:29]

Report message to a moderator

Re: Difference between two dates [message #330231 is a reply to message #330228] Sat, 28 June 2008 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And I feel he does not just want it for these dates:
Quote:
For example:


Wink

Regards
Michel
Re: Difference between two dates [message #330240 is a reply to message #330212] Sat, 28 June 2008 02:55 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

Rite michel i accept defently the logic is wrong we need to refine again.

kanish

[Updated on: Sat, 28 June 2008 02:56]

Report message to a moderator

Re: Difference between two dates [message #343451 is a reply to message #330212] Wed, 27 August 2008 05:39 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi All,



Please try This one...Is It give you Required Output I think Yes..please Reply Madhav

SELECT CONCAT
(CONCAT ( EXTRACT (YEAR FROM TO_DATE ('20-jul-2008'))
- EXTRACT (YEAR FROM TO_DATE ('10-may-2006'))
|| 'year,',
EXTRACT (MONTH FROM TO_DATE ('20-jul-2008'))
- EXTRACT (MONTH FROM TO_DATE ('10-may-2006'))
|| 'month,'
),
EXTRACT (DAY FROM TO_DATE ('20-jul-2008'))
- EXTRACT (DAY FROM TO_DATE ('10-may-2006'))
)
|| 'day' "result"
FROM DUAL;



Regards:
Gagan Deep Kaushal


Re: Difference between two dates [message #343489 is a reply to message #343451] Wed, 27 August 2008 07:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sat, 28 June 2008 07:42
What happens in your query if month or day of first date is greater than second one, for instance, 20-JUL-2006 and 10-MAY-2008?


In addition, your query is wrong:
SQL> SELECT CONCAT
  2  (CONCAT ( EXTRACT (YEAR FROM TO_DATE ('20-jul-2008'))
  3  - EXTRACT (YEAR FROM TO_DATE ('10-may-2006'))
  4  || 'year,',
  5  EXTRACT (MONTH FROM TO_DATE ('20-jul-2008'))
  6  - EXTRACT (MONTH FROM TO_DATE ('10-may-2006'))
  7  || 'month,'
  8  ),
  9  EXTRACT (DAY FROM TO_DATE ('20-jul-2008'))
 10  - EXTRACT (DAY FROM TO_DATE ('10-may-2006'))
 11  )
 12  || 'day' "result"
 13  FROM DUAL;
(CONCAT ( EXTRACT (YEAR FROM TO_DATE ('20-jul-2008'))
                                      *
ERROR at line 2:
ORA-01858: a non-numeric character was found where a numeric was expected

Regards
Michel

[Updated on: Wed, 27 August 2008 07:23]

Report message to a moderator

Re: Difference between two dates [message #343492 is a reply to message #343451] Wed, 27 August 2008 07:21 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
In addition to Michel's comments (and actually to reiterate his comments from further above), what about when the dates are slightly different i.e. test your calculation for accuracy with the dates-
20-jul-2008
10-aug-2006

Oooo, here's and idea. For all those posting supposed 'full' answers, go and have a read of the link that was posted in the SECOND POST of this thread.

[Updated on: Wed, 27 August 2008 07:21]

Report message to a moderator

Re: Difference between two dates [message #343737 is a reply to message #330212] Thu, 28 August 2008 00:17 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
HI All,
Exactly that logic was wrong.
You Can try This query but this eliminate the days in output as oracle does not mix Months and days hope this will help you :

SELECT EXTRACT(YEAR FROM (to_date('20-JUL-2008') - to_date('10-AUG-2006')) YEAR TO MONTH )
|| ' years '
|| EXTRACT(MONTH FROM (to_date('20-JUL-2008') - to_date('10-AUG-2006')) YEAR TO MONTH )
|| ' months ' "Interval"
FROM dual;

Regards:
Gagan Deep Kaushal
Re: Difference between two dates [message #343744 is a reply to message #343737] Thu, 28 August 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> SELECT EXTRACT(YEAR FROM (to_date('20-JUL-2008') - to_date('10-AUG-2006')) YEAR TO MONTH )
  2  || ' years '
  3  || EXTRACT(MONTH FROM (to_date('20-JUL-2008') - to_date('10-AUG-2006')) YEAR TO MONTH )
  4  || ' months ' "Interval"
  5  FROM dual;
SELECT EXTRACT(YEAR FROM (to_date('20-JUL-2008') - to_date('10-AUG-2006')) YEAR TO MONTH )
                                  *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Still wrong.

Regards
Michel
Re: Difference between two dates [message #343764 is a reply to message #343744] Thu, 28 August 2008 01:34 Go to previous messageGo to next message
gaganzk
Messages: 36
Registered: May 2008
Member
Hi Michel,

I dont know which platform of DB you are using but i use the same query on Oracle 10gO2 and ran successfully.



./fa/4897/0/


Regards:
Gagan Deep Kuashal
Re: Difference between two dates [message #343766 is a reply to message #343744] Thu, 28 August 2008 01:45 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
very convienient is the function MONTHS_BETWEEN for such an issue, but test the following case and use date format strings:
select MONTHS_BETWEEN(     to_date('04-AUG-2008','DD-MONTH-YYYY'), 
 to_date('10-AUG-2006','DD-MONTH-YYYY')) from dual;

--gives correct 23
select EXTRACT(MONTH from (to_date('24-AUG-2008','DD-MONTH-YYYY') 
- to_date('10-AUG-2006','DD-MONTH-YYYY')) YEAR TO MONTH) from dual;

--gives 0
select EXTRACT(MONTH from (to_date('25-AUG-2008','DD-MONTH-YYYY') 
- to_date('10-AUG-2006','DD-MONTH-YYYY')) YEAR TO MONTH) from dual;

--gives 1
Re: Difference between two dates [message #343778 is a reply to message #343764] Thu, 28 August 2008 02:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It does not matter it works for you, if it does not work for me it is wrong.
If you just think about the function you use, you will know why it does not work.

Regards
Michel
Previous Topic: Oracle Partitioning
Next Topic: update data from LONG to VArchar2
Goto Forum:
  


Current Time: Fri Dec 02 14:35:05 CST 2016

Total time taken to generate the page: 0.14816 seconds