SQL Server calculates the number of weeks and the number of weeks based on the Start week.

Because SQL server does not calculate the number of weeks and the number of weeks based on the Start Week (Monday is the first day of a week or Sunday, share with you.

I. Calculate the number of weeks based on the Start week: fn_getweekday

Create Function [DBO]. [fn_getweekday] (@ inputdate date, -- @ firstdayofweek value: 1 to 7. If it is 1, Monday indicates the first day of a week. If it is 7, Sunday indicates the first day of a week. @ Firstdayofweek Int = 1) returns intasbegin return datepart (DW, dateadd (D, @ datefirst-@ firstdayofweek, @ inputdate) End

Ii. Calculate the number of weeks based on the Start week: fn_getweeknumber

Create Function [DBO]. [fn_getweeknumber] (@ inputdate date, -- @ firstdayofweek) the value ranges from 1 to 7. If it is 1, Monday indicates the first day of a week. If it is 7, Sunday indicates the first day of a week. @ Firstdayofweek Int = 1) returns intasbegin declare @ weekdayofnewyearday int set @ weekdayofnewyearday = [DBO]. fn_getweekday (datename (year, @ inputdate) + '-01-01', @ firstdayofweek) return ceiling (datepart (dy, @ inputdate)-(@ firstdayofweek-@ response + 1 )) /7) + 1end

Note: The above functions are not affected by the cultural settings and @ datefirst global variables. In any case, the correct results can be returned.