SET datefirst change in SQL Server

in SQL Server by default, the start of each week starts from Sunday , if the default is Monday?

Here are three ways to solve this problem:changing the new DimDate directly through SET datefirst VALUE, and then associating it with the Date Key each time you need to calculate Week number separately, but this requires constant JOIN DimDate, each record To get a LookUp againTwo:when you need to use the Week number in the stored procedure, set Datefirst first and then use the DATEPART () function to get the Week number

SET Datefirst 1   

Three: write a function directly, each time you call it.

SELECT @ @DATEFIRST  --7select datename (WEEK, ' 2013-12-31 ') as Weekname  --53SELECT datename (WEEK, ' 2014-01-01 ') As Weekname  --1SELECT datename (WEEK, ' 2014-01-05 ') as Weekname  --2

Code:

1. Create a table:

IF object_id (' dimdatestartwithmonday ', ' U ') is not nulldrop table Dimdatestartwithmondaygocreate table Dimdatestartwithmonday (    datekey INT PRIMARY KEY,    fulldate DATE not NULL,    [Datename] NVARCHAR (),    Daynumberofweek TINYINT NOT NULL,    Daynameofweek NVARCHAR (TEN) not NULL,    daynumberofmonth TINYINT not NULL,    Daynumberofyear SMALLINT NOT NULL,     weeknumberofyear TINYINT not NULL,    EnglishMonthName NVARCHAR (TEN) NOT NULL,    monthnumberofyear TINYINT not NULL,    CalendarQuarter TINYINT NOT NULL,    calendarsemester TINYINT not NULL,    calendaryear SMALLINT not NULL)

2. Inserting values

DECLARE @StartDate datetimedeclare @EndDate datetimeselect @StartDate = ' 2001-01-01 ', @EndDate = ' 2035-12-31 ' Whil E (@StartDate <[email protected]) BEGIN INSERT into Dimdatestartwithmonday (DateKey, Ful         LDate, [Datename], Daynumberofweek, Daynameofweek, Daynumberofmonth, Daynumberofyear, Weeknumberofyear, EnglishMonthName, Monthnumberofyear, CalendarQuarter, calendarsemest Er, calendaryear) SELECT CAST (CONVERT (VARCHAR (8), @StartDate,) as INT) as DateKey, CONVERT (Varc HAR (Ten), @StartDate, fulldate) as, CONVERT (VARCHAR), @StartDate, 106) as [Datename], DATEPART (dw,@ StartDate) as Daynumberofweek, Datename (DW, @StartDate) as Daynameofweek, Datename (DD, @StartDate) as [D           Ayofmonth], Datename (DY, @StartDate) as [DayOfYear], DATEPART (WW, @StartDate) as Weeknumberofyear, Datename (MM, @StarTdate) as EnglishMonthName, DATEPART (MM, @StartDate) as Monthnumberofyear, DATEPART (QQ, @StartDate) as C Alendarquarter, Case-DATEPART (MM, @StartDate) between 1 and 6 then 1 ELSE 2 END as CalendarSemester, DATEPART (YY, @StartDate) as calendaryear SET @StartDate = @StartDate + 1 E        ND

 3. Custom Function

   IF object_id (' Etlwork_getweeknumber ', ' FN ') is not nulldrop function Etlwork_getweeknumbergo CREATE function etlwork_get WeekNumber (@DATE datetime) RETURNS integerasbegin DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD (Yyyy,datediff (yyyy,0, @DATE), 0) DECLARE @WEEK_NUMBER INTEGER--If the current time is the first day of the current year if @DATE = @FIRST_DATE_OF_YEAR SET @WEEK_NUMBER = 1--Sunday is the first day of the year. ELSE IF (DATEPART (WEEKDAY, @DATE) = 1 and DATEDIFF (DayOfYear, @FIRST_DATE_OF_YEAR, @DATE)/7 + 1 = DAT Epart (WEEK, @DATE)) SET @WEEK_NUMBER = DATEPART (WEEK, @DATE)-Sunday is not the case for the first day of the year ELSE IF (DATEPART (WEEKDAY, @DAT E) = 1 and DATEDIFF (DayOfYear, @FIRST_DATE_OF_YEAR, @DATE)/7 + 1 <> DATEPART (WEEK, @DATE)) SET @WEEK_NUMBER = D Atepart (WEEK, @DATE)-1--if the last Sunday of the current day is less than the first day of the year ELSE if DATEADD (Day,-1,dateadd (Wk,datediff (wk,0, @DATE), 0)) < @FIRST _date_of_year SET @WEEK_NUMBER = 1-a Sunday in front of the current day happens to be 7 times times the number of days in Sunday for the beginning of the year ELSE IF DATEDIFF (DayOfYear, @FIRST_DATE_O F_year,dateadd (day,-1,dateADD (Wk,datediff (wk,0, @DATE), 0))/7 + 1 = DATEPART (WEEK, @DATE) SET @WEEK_NUMBER = DATEPART (WEEK, @DATE) + 1 EL SE SET @WEEK_NUMBER = DATEPART (WEEK, @DATE) RETURN @WEEK_NUMBEREND

Test:

DECLARE @DATE datetime = ' 2017-01-02 ' DECLARE @FIRST_DATE_OF_YEAR datetime = DATEADD (Yyyy,datediff (yyyy,0, @DATE), 0) SELECT DATEPART (WEEK, @DATE),--Week of the year, the default starts in Sunday        DATEADD (Wk,datediff (wk,0, @DATE), 0),--the current week of Monday, the default starting from Sunday, but still find Monday       DATEADD (Day,-1,dateadd (Wk,datediff (wk,0, @DATE), 0)),--the current week to find Monday, and then the day before the Sunday       DATEDIFF (DayOfYear, @FIRST_DATE_OF_YEAR, DATEADD (Day,-1,dateadd (Wk,datediff (wk,0, @DATE), 0)),--the interval between       the first day of the current day off the year DATEDIFF (DayOfYear, @FIRST_DATE_OF_YEAR, DATEADD (Day,-1,dateadd (Wk,datediff (wk,0, @DATE), 0))/7 + 1  --week count by day

SET datefirst change in SQL Server