SqlServer中 SET DATEFIRST更改

標籤：ast   定義函數   style   tiny   預設   sel   需要   first   cal   

在 SQL Server 中預設情況下，每周的開始都是從周日開始算起的，如果預設星期一呢？

這裡有三種方式可以解決這個問題：一：直接通過 SET DATEFIRST VALUE 來更改重建新的 DimDate，然後每次需要單獨計算 Week Number 的時候根據 Date Key 關聯一下就可以了，但這樣就需要不斷 JOIN DimDate，每一條記錄都要 LookUp 一遍二：在預存程序中需要使用到  Week Number 的時候，就先設定一下 SET DATEFIRST 然後在使用 DATEPART() 函數來擷取 Week Number

SET DATEFIRST 1   --定義日期周一開始 

三：直接寫一個函數，每次調用一下就可以了

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

 代碼：

1.建立表：

IF OBJECT_ID(‘DimDateStartWithMonday‘,‘U‘) IS NOT NULLDROP TABLE DimDateStartWithMondayGOCREATE TABLE DimDateStartWithMonday(    DateKey INT PRIMARY KEY,    FullDate DATE NOT NULL,    [DateName] NVARCHAR(20),    DayNumberOfWeek TINYINT NOT NULL,    DayNameOfWeek NVARCHAR(10) NOT NULL,    DayNumberOfMonth TINYINT NOT NULL,    DayNumberOfYear SMALLINT NOT NULL,     WeekNumberOfYear TINYINT NOT NULL,    EnglishMonthName NVARCHAR(10) NOT NULL,    MonthNumberOfYear TINYINT NOT NULL,    CalendarQuarter TINYINT NOT NULL,    CalendarSemester TINYINT NOT NULL,    CalendarYear SMALLINT NOT NULL )

 2.插入值

DECLARE @StartDate DATETIMEDECLARE @EndDate DATETIMESELECT @StartDate = ‘2001-01-01‘,       @EndDate = ‘2035-12-31‘   WHILE(@StartDate<[email protected])   BEGIN        INSERT INTO DimDateStartWithMonday     (        DateKey,        FullDate,        [DateName],        DayNumberOfWeek,        DayNameOfWeek,        DayNumberOfMonth,        DayNumberOfYear,         WeekNumberOfYear,        EnglishMonthName,         MonthNumberOfYear,        CalendarQuarter,        CalendarSemester,        CalendarYear     )    SELECT CAST(CONVERT(VARCHAR(8),@StartDate,112) AS INT) AS DateKey,           CONVERT(VARCHAR(10), @StartDate,20) AS FullDate,           CONVERT(VARCHAR(20), @StartDate,106) AS [DateName],           DATEPART(DW,@StartDate) AS DayNumberOfWeek,           DATENAME(DW,@StartDate) AS DayNameOfWeek,           DATENAME(DD,@StartDate) AS [DayOfMonth],           DATENAME(DY,@StartDate) AS [DayOfYear],             DATEPART(WW,@StartDate) AS WeekNumberOfYear,           DATENAME(MM,@StartDate) AS EnglishMonthName,           DATEPART(MM,@StartDate) AS MonthNumberOfYear,           DATEPART(QQ,@StartDate) AS CalendarQuarter,           CASE WHEN DATEPART(MM,@StartDate) BETWEEN 1 AND 6                    THEN 1                ELSE 2           END AS CalendarSemester,           DATEPART(YY,@StartDate) AS CalendarYear SET @StartDate = @StartDate + 1   END       

 3.自訂函數

   IF OBJECT_ID(‘ETLWORK_GETWEEKNUMBER‘,‘FN‘) IS NOT NULLDROP FUNCTION ETLWORK_GETWEEKNUMBERGO CREATE FUNCTION ETLWORK_GETWEEKNUMBER(@DATE DATETIME)  RETURNS INTEGERASBEGIN  DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0)   DECLARE @WEEK_NUMBER INTEGER    -- 如果目前時間是當前年的第一天    IF @DATE = @FIRST_DATE_OF_YEAR        SET @WEEK_NUMBER = 1     -- 星期天是年第一天的情況    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 = DATEPART(WEEK,@DATE))          SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)      -- 星期天不是年第一天的情況    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 <> DATEPART(WEEK,@DATE))         SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) - 1     -- 如果當前天的上一個周日小於年第一天    ELSE IF DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)) < @FIRST_DATE_OF_YEAR         SET @WEEK_NUMBER = 1     -- 當前天前面的一個周日正好是以周日為開始年的 7 倍的天數    ELSE IF DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1 = DATEPART(WEEK,@DATE)         SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) + 1      ELSE         SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)                  RETURN @WEEK_NUMBEREND

 測試：

DECLARE @DATE DATETIME = ‘2017-01-02‘DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0) SELECT DATEPART(WEEK,@DATE), -- 一年中的周數，預設以周日開始        DATEADD(WK,DATEDIFF(WK,0,@DATE),0), -- 當前周的周一，預設從周日開始，但是仍然找周一       DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)), -- 當前周先找周一，然後往前一天找到周日       DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0))), -- 當前天離年第一天的間隔       DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1  -- 按天計算的周數

 

 

SqlServer中 SET DATEFIRST更改