I've looked (as I'm a subscriber) at your SQL Server Magazine articles on date calculations, but I'm still struggling!

I'm trying to calculate the number of months between two dates (in my case to work out how many standing order payments should have been received). For example:

Start Date - 30/10/2008

If End Date is, I want it to return:

29/4/2009 - 6
30/4/2009 - 7
1/5/2009 - 7

Thank you for any time you may be able to give.

Regards,

Barry

barry@idealsoftware.co.uk1/26/2009 6:55:11 AM

ukribe1, thanks for the suggestion about adding links to all the article's in Itzik's popular DATETIME series. I'll add a box with the article links.
Anne Grubb, Web Lead Editor, Windows IT Pro and SQL Server Magazine

Anne6/28/2007 8:25:36 AM

Great Article. Just a quick point about the website: why are the other articles in this series not available from the "Related Articles" listing?

Richard6/28/2007 5:38:08 AM

good article

Cid6/4/2007 10:30:29 AM

I found a neat trick several years ago for calculating the age in years between two dates. It takes advantage of integer truncation on division, and works for leap years as well. Working through an example:
(20070601 - 19710212) / 10000
360389 / 10000
36

SELECT (ABS(CONVERT(INT,(CONVERT(CHAR(8),@i_Date2,112))) - CONVERT(INT,(CONVERT(CHAR(8),@i_Date1,112))))) / 10000

[Itzik: This is pretty cool!]

Craig6/1/2007 8:13:23 PM

We're working to get a new version of the table published. (Diana)

Note from Itzik on the date error in Table 2:
Regarding Table 2, it’s a typo. I just inspected the original article I sent, and this is what I had (and should be):

Table 2: Next birthday date of employees
LastName FirstName BirthDate NextBirthDay
-------------------- ---------- ----------------------- -----------------------
Leaping George 1972-02-29 00:00:00.000 2007-03-01 00:00:00.000

Diana6/1/2007 10:34:44 AM

Interesting set of articles on date calculations - for age calculation I like to normalize the birthday to the current year and determine if that date has passed yet; seems to work for leap year as well:
select bdate,
datediff(year,bdate,getdate()) -
case
when dateadd(year,datediff(year,bdate,getdate()),bdate) > getdate() then 1
else 0 end
from ...

Mike5/24/2007 8:57:06 AM