there is always something to develop
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
Comments
"If you don't start, it's certain you won't arrive. "
You are 8039 reader
since 1st April 2009
Yes | 100.0% |
6 |
No | 0 |