Difference between two dates [message #330212] |
Fri, 27 June 2008 23:52  |
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 #330215 is a reply to message #330212] |
Sat, 28 June 2008 00:29   |
mm_kanish05
Messages: 493 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 #343451 is a reply to message #330212] |
Wed, 27 August 2008 05:39   |
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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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   |
pablolee
Messages: 2882 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   |
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 #343766 is a reply to message #343744] |
Thu, 28 August 2008 01:45   |
_jum
Messages: 577 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  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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
|
|
|