MS SQL Tips & Tricks

How to get the calendar week number regardless @@DATEFIRST setting

To calculate calendar week number from a given date we need to follow couple of steps. First we need to find out what is a week day of first day of the year. Because of @@DATEFIRST setting that can have different value in various MS SQL Server installations, we need to normalize it first. That is exactly what is done in @cFirstDay calculation. @cFirstDay is the number of first week day of the year. Then we just need to shift a current date and divde it by 7. 

declare @cDate datetime;
set @cDate = '2009-01-11'

declare @cYearStart  datetime;
declare @cFirstDay int;

set @cYearStart = dateadd(month,((year(@cDate)-1900)*12),0);
select  @cFirstDay=(@@DATEFIRST-1)+DATEPART(dw, @cYearStart)- 
  case 

   when (@@DATEFIRST-1)+
		   DATEPART(dw, @cYearStart)>7 then 7 
   else 0 

  end 
  
select  ((datediff(d,@cYearStart,@cDate)+(7-@cFirstDay))/7)+1 
	as CalendarWeekNo

And again the same as above but complied in one query 

declare @cDate datetime;
set @cDate = '2009-01-11'

select  ((datediff(d,dateadd(month,
				((year(@cDate)-1900)*12),0
			),@cDate)+
	(7-((@@DATEFIRST-1)+
		DATEPART(dw, 
			dateadd(month,
				((year(@cDate)-1900)*12),0)
				)- 
   case 
   when (@@DATEFIRST-1)+
	   DATEPART(dw, dateadd(month,
		((year(@cDate)-1900)*12),0)
		)>7 then 7 
   else 0 
   end )))/7)+1

See also


Learn more tricks

Add Comment

Comments