Home » Pages » SQL » Microsoft SQL Server

Week number in a Month

By Sergey - Posted on December 25th, 2007
Tagged:

This is sample code. Add error handling and adjust to your requirements as necessary.

DECLARE @dt DATETIME, @WeekOfMonth TINYINT
SET @dt = '2007-07-08'
 
SET @WeekOfMonth = (DAY(@dt) + 
				(DATEPART(dw, DATEADD (MONTH, DATEDIFF (MONTH, 0, @dt), 0)) 
				  --^-- The day of the week for the first day of month
			-1) -- # of days to add to make the first week full 7 days
		-1)/7 + 1 
PRINT @WeekOfMonth

Another way
By Anonymous (not verified) - Posted on July 25th, 2008

Try this too:

DECLARE @dt DATETIME, @WeekOfMonth TINYINT
SET @dt = '2007-07-08'
SET @WeekOfMonth = DATEDIFF(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @dt), 0), @dt) +1
PRINT @WeekOfMonth

Shorter and simpler
By Sergey - Posted on July 26th, 2008

Yes, it's shorter and simpler.

Thanks.