Identify difference in Days/Months between two dates based on the past month

Question

I have to implement a solution where the difference betweeen the two dates can be in months or days.

For e.g. I have two accounts opened on dates date1 and date2. I should find if the date diff is more than a month or less than a month.

Instance 1 -

Date1 - 30-01-2007
Date2 - 01-03-2007

Although date diff is 30 days, which is less than a month, but if you see entire Feb month is passed hence it should fall in 1 month range.

Instance 2 -

Date1 - 26-11-2007
Date2 - 26-12-2007

Here date diff is 30 days, Since month getting elapsed is Nov 2007, it should be compared with 30. Hence it is 1 month

Instance 3 -

Date1 - 16-02-2010
Date2 - 17-03-2010

Although date diff is 29 days, since month getting elapsed is feb 2010. it should be compared with 28. Hence it is 1 month again.

Datediff does not give correct results. for the below mentioned datediff, result is 1. But the result should be actually 2.

select datediff(month,'2007-01-01','2007-02-28')

Please help.

http://stackoverflow.com/questions/1106945/calculating-number-of-full-months-between-two-dates-in-sql — inser, Mar 18 '13 at 14:26
Has your question been answered? Or do you require more help? — Tom Bowen, Mar 20 '13 at 21:59

score 0 · Answer 1 · answered Mar 18 '13 at 14:59

You're going to have to do it manually.

Take use date diff, but pass in number of days. ie:

select datediff(day,'2007-01-01','2007-02-28')

Use this number, and find out which month is is. ie:

select datepart(month,'2007-01-01')

Then use a case statement depending on the month, and the number of days passed.

1 Answers1