SQL DateDiff Weeks - Need and alternative

Question

The MS SQL DateDiff function counts the number of boundaries crossed when calculating the difference between two dates.

Unfortunately for me, that's not what I'm after. For instance, 1 June 2012 -> 30 June 2012 crosses 4 boundaries, but covers 5 weeks.

Is there an alternative query that I can run which will give me the number of weeks that a month intersects?

UPDATE

To try and clarify exactly what I'm after:

For any given month I need the number of weeks that intersect with that month.

Also, for the suggestion of just taking the datediff and adding one, that won't work. For instance February 2010 only intersects with 4 weeks. And the DateDiff calls returns 4, meaning that simply adding 1 would leave me the wrong number of weeks.

Answer 1

Beware: Proper Week calculation is generally trickier than you think!

If you use Datepart(week, aDate) you make a lot of assumptions about the concept 'week'. Does the week start on Sunday or Monday? How do you deal with the transition between week 1 and week 5x. The actual number of weeks in a year is different depending on which week calculation rule you use (first4dayweek, weekOfJan1 etc.)

if you simply want to deal with differences you could use

DATEDIFF('s', firstDateTime, secondDateTime) > (7 * 86400 * numberOfWeeks)

if the first dateTime is at 2011-01-01 15:43:22 then the difference is 5 weeks after 2011-02-05 15:43:22

EDIT: Actually, according to this post: Wrong week number using DATEPART in SQL Server You can now use Datepart(isoww, aDate) to get ISO 8601 week number. I knew that week was broken but not that there was now a fix. Cool!

THIS WORKS if you are using monday as the first day of the week

 set language = british
 select datepart(ww, @endofMonthDate) - 
        datepart(ww, @startofMonthDate) + 1 

1. Datepart is language sensistive. By setting language to british you make monday the first day of the week.
2. This returns the correct values for feburary 2010 and june 2012! (because of monday as opposed to sunday is the first day of the week).
3. It also seems to return correct number of weeks for january and december (regardless of year). The isoww parameter uses monday as the first day of the week, but it causes january to sometimes start in week 52/53 and december to sometimes end in week 1 (which would make your select statement more complex)

Answer 2

SET DATEFIRST is important when counting weeks. To check what you have you can use select @@datefirst. @@datefirst=7 means that first day of week is sunday.

set datefirst 7

declare @FromDate datetime = '20100201'
declare @ToDate datetime = '20100228'

select datepart(week, @ToDate) - datepart(week, @FromDate) + 1

Result is 5 because Sunday 28/2 - 2010 is the first day of the fifth week.

If you want to base your week calculations on first day of week is Monday you need to do this instead.

set datefirst 1

declare @FromDate datetime = '20100201'
declare @ToDate datetime = '20100228'

select datepart(week, @ToDate) - datepart(week, @FromDate) + 1

Result is 4.