首页 > 代码库 > SqlServer中 SET DATEFIRST更改
SqlServer中 SET DATEFIRST更改
在 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<+@EndDate) 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更改
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。