Month difference

Database

Month difference

Hi!
Someone knows how to calculate month difference between two dates?

For example:

=> date '2006-01-01' - date '2006-03-01' = 2 months

I know that I could calculate by division, but it could be not exactly.

=> date '2006-01-01' - date '2006-03-01' /30

Thanks a lot.

Natalia.
3 REPLIES
Enthusiast

Re: Month difference

date '2006-01-01' - date '2006-03-01' is not 2 it is -2
try this
select date '2006-03-01' - date '2006-01-01' month as ActualMonths
the result will be 2

but the problem with this operation is

select date '2006-03-22' - date '2006-01-01' month as ActualMonths
the answer is 2 again.

It will not display the data as 2 months XX days.

Re: Month difference

Thanks. :-)

Re: Month difference

select (extract (year from '2006-03-22' )-extract(year from '2005-01-01'))*12 + extract (month from '2006-03-22') - extract (month from '2005-01-01')

14 months

select (extract (year from '2006-01-22' )-extract(year from '2005-03-01'))*12 + extract (month from '2006-01-22') - extract (month from '2005-03-01')

10 months

Thanks,
ksheersagar