Here is how to calculate number of weeks in a month according to ISO 8601. I present it both in SQL Server syntax and VBScript. Haven't run large tests but checked some years.
Also, the second part shows how to get date's ISO week number in one line(no recursion), which is missing from SQL Server (Oracle has it).
Warning: if your language has the datetime data type as seconds(or hours..) be careful, because you must check how summer time affects subtractions. Both SQL and VBS have day as the integer part.
I can give explanations about these statements if you need.
Cheers
Number of ISO weeks:
-- SQL Server mindate Monday 1753-01-01 doesn't really matter
--
declare @theFirstDOMonth datetime
set nocount on
SET @theFirstDOMonth='1996-02'+'-01'
--select DATEADD(mm,1,@theFirstDOMonth) - 1 - ((7+((CONVERT(int,DATEADD(mm,1,@theFirstDOMonth)-4)-0)%7))%7) as LastThursday
select 1 + FLOOR(DATEDIFF(dd,@theFirstDOMonth,
DATEADD(mm,1,@theFirstDOMonth) - 1 - ((7+((CONVERT(int,DATEADD(mm,1,@theFirstDOMonth)-4)-0)%7))%7)) / 7 )as isoweeksinmonth
/* VBScript
Function Main()
dim theFirstDOMonth
theFirstDOMonth=DateValue( "1996-02"&"-01" )
msgbox "Number of weeks in that month " & 1+ Int( ( DateAdd( "M" , 1 , theFirstDOMonth ) - Weekday( DateAdd( "M" , 1 , theFirstDOMonth ) - 4, 2) - theFirstDOMonth ) / 7 )
Main = DTSTaskExecResult_Success
End Function
*/
ISO Week number:
--
declare @theDate datetime
set nocount on
SET @theDate='1900-01-01'
select DATEPART(yy,@theDate + 3 - ((7+((CONVERT(int,@theDate)-0)%7))%7)) as isowyear
select 1+((7+((CONVERT(int,@theDate)-0)%7))%7) as isowday
select 1+FLOOR(DATEDIFF(dd,CONVERT(datetime,STR(DATEPART(yy,@theDate + 3 - ((7+((CONVERT(int,@theDate)-0)%7))%7)))+'-01-01'),(@theDate + 3 - ((7+((CONVERT(int,@theDate)-0)%7))%7)))/7) as isoweek
/* VBScript
Function Main()
dim theDate
theDate=DateValue( "2005-01-01" )
msgbox "ISO Week Year is " & Year( theDate + 4 - Weekday( theDate, 2 ) ) &Chr(13)&_
"ISO Week Day is " & Weekday( theDate, 2 ) &Chr(13)&_
"ISO Week is " & 1 + Int( ( ( theDate + 4 - Weekday( theDate, 2 ) ) - DateSerial( Year( theDate + 4 - Weekday( theDate, 2 ) ), 1, 1) ) / 7 )
Main = DTSTaskExecResult_Success
End Function
*/