## 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
```

Learn more tricks

Add Comment

Comments