-
SQL Server -
把星期一
(
周一
)
当作每个星期的开始在一年中求取周数
先感叹一句!好长时间没有更新博客了!偶尔看到一句话,觉
得被电击了
-
庸人败于懒,能人败于傲!
因此,不能再懒惰了!
今天想写一个有关计算
Week
Number
的函数,刚开始觉得应该很简单,凭着感觉七写
八写到最后发现越
写越乱,
到最后搞了快两个小时以为解决了,
结果一测还有好多数据不正确。
非常有挫败感!
感觉很不服气,
觉得很丢人,跑出去站了会,冷静下来,重新拿起纸笔认真的分
析了一下,连写到测试快半个小时还是解决
了。
在
SQL
Server
中默认情况下
,每周的开始都是从周日开始算起的。但是在国内也有不一样的要求,比如按
照习惯往往
要求每周从周一算起。这样一来之前在数据仓库中的
Week
Number
可能就不准确了,因为可
能很多时候都是按默认方式来生成
Week
Number
的。
这里有三种方式可以解决这个问题:
第一种方式是直接通过
SET
DATEFIRST
VALUE
来更改重新生成新的
DimDate
,然后每次需要单独计算
Week
Number
的时候根据
Date
Key
关联一下就可以了,
但这样就需要不断
JOIN
DimDate
,
每一条记
录都要
LookUp
一遍,不太好。
第二种方式就是在存储过程中需要使用到
Week
Number
的时候,就先设置一下
SET
DATEFIRST
然后
在使用
DATEPART()
函数来获取
Week
Number
。这种方式需
要每次都显示的
SET
DATEFIRST
,不太
方便。
第三种方式就是直接写一个函数,每次调用一下就可以了。
关于使用
SET
DATEFIRST
-
的值从
1
到
7
,即周一到周日,默认值是
7
。
-- The default first date
in a week is Sunday, the value is 7
SELECT
@@DATEFIRST
-- Default DATEFIRST is
Sunday
SELECT
DATENAME
(WEEK,
'2013-
12-31'
)
AS
WeekName
-- 53
SELECT
DATENAME
p>
(WEEK,
'2014-01-01'
)
AS
WeekName
--
1
SELECT
< br>DATENAME
(WEEK,
'2014-01-05
'
)
AS
WeekName
-- 2
-- Change the DATEFIRST to 1, Monday
will be the first day of week.
SET
DATEFIRST
1
SELECT
@@DATEFIRST
--
1
-- After
change the DATEFIRST to Monday
SELECT
DATENAME
p>
(WEEK,
'2013-12-31'
)
AS
WeekName
--
53
SELECT
DATENAME
(WEEK,
'2014-01-0
1'
)
AS
WeekName
-- 1
SELECT
DATENAME
p>
(WEEK,
'2014-01-05'
)
AS
WeekName
--
1
要注意的是
SET
DATEFIRST
只在当
前执行中有效,
也就说比如新开一个查询页面继续查询
SELECT
@@D
ATEFIRST
则还是显示默认值
7
。
在创建时间维度的代码中添加
SET
DATEFIRST
1
,表示每周以周一开始。
USE
BIWORK_SSIS
GO
SET
NOCOUNT
ON
--
设置每周的起始天为周一
SET
DATEFIRST
1
IF
OBJECT_ID
(
'DimDateStartWithMonday'
< br>,
'U'
)
IS
NOT
NULL
DROP
TABLE
DimDateStartWithMonday
GO
CREATE
TABLE
DimDateStartWithMonday
(
DateKey
INT
PRIMARY
KEY
,
FullDate
DATE
NOT
NULL
,
[DateName]
NVARCH
AR
(
20
),
DayNumberOfWeek
TINYINT
NOT
NULL
,
DayNameOfWeek
NVARCHAR
(
10
)
NOT
NULL
,
DayNumberOfMonth
TINYINT
NOT
NULL
,
DayNumberOfYear
SMALLINT
NOT
NULL
,
WeekNumberOfYear
TINYINT
NOT
NULL
,
EnglishMonthName
NVARCHAR
(
p>
10
)
NOT
NULL
,
MonthNumberOfYear
TINYINT
NOT
NULL
,
CalendarQuarter
TINYINT
NOT
NULL
,
CalendarSemester
TINYINT
NOT
NULL
,
CalendarYear
SMALLINT
NOT
NULL
)
DECLARE
@StartDate
DATETIME
DECLARE
@EndDate
DATETIME
SELECT
@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
(
VAR
CHAR
(
8
),
@StartDate
,
112
)
AS
INT
)
AS
DateKey,
CONVERT
(
VARCHAR
p>
(
10
),
@S
tartDate
,
20
)
AS
FullDate,
CONVERT
(
VARCHAR
p>
(
20
),
@S
tartDate
,
106
)
AS
[DateName]
,
DATEPART
(DW,
@Star
tDate
)
AS
DayNumberOfWeek,
DATENAME
(DW,
@StartDate
)
AS
DayNameOfWeek,
DATENAME
(DD,
@StartDate
)
AS
[DayOfMonth]
,
DATENAME
(DY,
@Star
tDate
)
AS
[DayOfYear]
,
DATEPART
(WW,
@Star
tDate
)
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
GO