SQL Server時間粒度系列----第2節日期、周時間粒度詳解

 1 IF OBJECT_ID(N'dbo.ufn_Days', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_Days;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 獲得指定日期時間基於基準日期的總天數(一個整數值）
 9 -- 說明: 如果指定的日期時間為NULL或者小於基準日期“1900-01-01”時，則其值預設基準日期
10 --       結果值為非負整數，從0開始計數。
11 -- 作者: XXX
12 -- 創建: yyyy-MM-dd
13 -- 修改: yyyy-MM-dd XXX 修改內容描述
14 -- 調用: SET @intDays = dbo.ufn_Days('2008-01-14')
15 --==================================
16 CREATE FUNCTION dbo.ufn_Days
17 (
18     @dtmDate DATETIME                    -- 指定的日期時間
19 ) RETURNS INT
20     --$Encode$--
21 AS
22 BEGIN
23     IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'
24     BEGIN
25         SET @dtmDate = '1900-01-01';
26     END
27  
28     -- datepart參數也可以為dd或d
29     RETURN DATEDIFF(DAY, '1900-01-01', @dtmDate)
30 END
31 GO
32  
33 IF OBJECT_ID(N'dbo.ufn_Days2Date', 'FN') IS NOT NULL
34 BEGIN
35     DROP FUNCTION dbo.ufn_Days2Date;
36 END
37 GO
38  
39 --==================================
40 -- 功能: 獲得一個整數值基於基準日期對應的日期
41 -- 說明: 如果指定的整數值為NULL或為負整數時，則其值預設為0；
42 --       如果指定的整數值大於“9999-12-31”對應的整數值時，則其值預設設置為“9999-12-31”對應的整數值
43 --       結果值為從基準日期開始計數的日期
44 -- 作者: XXX
45 -- 創建: yyyy-MM-dd
46 -- 修改: yyyy-MM-dd XXX 修改內容描述
47 -- 調用: SET @dtmDate = dbo.ufn_Days2Date(39459) --'2008-01-14'
48 --==================================
49 CREATE FUNCTION dbo.ufn_Days2Date 
50 (
51     @intDays INT                -- 指定的整數值
52 ) RETURNS DATETIME
53     --$Encode$--
54 AS
55 BEGIN
56     IF @intDays IS NULL OR @intDays < 0
57     BEGIN
58         SET @intDays = 0;
59     END
60  
61     DECLARE @intMaxDays AS INT;
62     SET @intMaxDays = dbo.ufn_Days('9999-12-31');
63  
64     IF @intDays >= @intMaxDays
65     BEGIN
66         SET @intDays = @intMaxDays;
67     END
68  
69     -- datepart參數也可以為dd或d
70     RETURN DATEADD(DAY, @intDays, '1900-01-01');
71 END
72 GO

1 SELECT dbo.ufn_Days(NULL) AS 'NULL值對應的整數值', dbo.ufn_Days('1899-12-31') AS '小於1900-01-01對應的整數值', dbo.ufn_Days('1900-01-01') AS '1900-01-01對應的整數值', dbo.ufn_Days('2016-01-07') AS '2016-01-07對應的整數值', dbo.ufn_Days('9999-12-31') AS '9999-12-31對應的整數值';
2 SELECT dbo.ufn_Days2Date(NULL) AS 'NULL值對應的日期', dbo.ufn_Days2Date(-1) AS '小於0對應的日期', dbo.ufn_Days2Date(0) AS '0對應的日期', dbo.ufn_Days2Date(42374) AS '42374對應的日期', dbo.ufn_Days2Date(2958463) AS '2958463對應的整數值', dbo.ufn_Days2Date(2958464) AS '大於2958463對應的整數值';
3 GO

執行後的查詢結果如下圖：

 1 IF OBJECT_ID(N'dbo.ufn_WeekOfYear', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_WeekOfYear;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 獲取指定日期屬於當前第幾周
 9 -- 說明: 一年最多1-53周
10 -- 作者: XXX
11 -- 創建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改內容描述
13 -- 調用: SELECT dbo.ufn_WeekOfYear('2016-01-07');
14 --==================================
15 CREATE FUNCTION dbo.ufn_WeekOfYear
16 (
17     @dtmDate DATETIME
18 ) RETURNS TINYINT
19     --$Encode$--
20 AS
21 BEGIN
22     -- datepart參數也可以為wk, ww
23     RETURN CAST(DATEPART(WEEK, @dtmDate) AS TINYINT)
24 END
25 GO
26  
27 IF OBJECT_ID(N'dbo.ufn_WeekdayNameOfWeek', 'FN') IS NOT NULL
28 BEGIN
29     DROP FUNCTION dbo.ufn_WeekdayNameOfWeek;
30 END
31 GO
32  
33 --==================================
34 -- 功能: 獲取指定日期屬於當前周周幾的名稱
35 -- 說明: 結果值的展示形式會以來預設設置語言
36 -- 作者: XXX
37 -- 創建: yyyy-MM-dd
38 -- 修改: yyyy-MM-dd XXX 修改內容描述
39 -- 調用: SELECT dbo.ufn_WeekOfYear('2016-01-07');
40 --==================================
41 CREATE FUNCTION dbo.ufn_WeekdayNameOfWeek
42 (
43     @dtmDate DATETIME
44 ) RETURNS NVARCHAR(20)
45     --$Encode$--
46 AS
47 BEGIN
48     -- datepart參數也可以為dw
49     RETURN DATENAME(WEEKDAY, @dtmDate);
50 END
51 GO
52  

測試以上函數效果的T-SQL代碼如下：

SET LANGUAGE N'us_english';
SELECT @@LANGUAGE, dbo.ufn_WeekOfYear('2017-02-01'), dbo.ufn_WeekdayNameOfWeek('2017-02-01')
GO
 
SET LANGUAGE N'簡體中文';
SELECT @@LANGUAGE, dbo.ufn_WeekOfYear('2017-02-01'), dbo.ufn_WeekdayNameOfWeek('2017-02-01')
GO

 1 IF OBJECT_ID(N'dbo.ufn_WeekdayOfWeek', 'FN') IS NOT NULL
 2 BEGIN
 3     DROP FUNCTION dbo.ufn_WeekdayOfWeek;
 4 END
 5 GO
 6  
 7 --==================================
 8 -- 功能: 獲取指定日期隸屬當前周周幾
 9 -- 說明: 結果值從1到7，分別對應從周一到周日，該值與@@DATEFISRT配置函數值保持一致
10 -- 作者: XXX
11 -- 創建: yyyy-MM-dd
12 -- 修改: yyyy-MM-dd XXX 修改內容描述
13 -- 調用: SELECT dbo.ufn_WeekdayOfWeek('2017-01-07') -- 4（表示星期四）
14 --==================================
15 CREATE FUNCTION dbo.ufn_WeekdayOfWeek
16 (
17     @dtmDate DATETIME                -- 指定的日期時間
18 ) RETURNS TINYINT
19     --$Encode$--
20 BEGIN
21     DECLARE
22         @tintDateFirst AS TINYINT,
23         @tintWeekDayIndexID AS TINYINT,
24         @tintSum AS TINYINT;
25  
26     SELECT
27         @tintDateFirst = @@DATEFIRST,
28         @tintWeekDayIndexID = DATEPART(WEEKDAY, @dtmDate),
29         @tintSum = @tintDateFirst + @tintWeekDayIndexID;
30  
31     RETURN (CASE WHEN @tintSum >= 9 THEN @tintSum - 8 WHEN @tintSum = 8 THEN 7 ELSE @tintSum - 1 END);    
32 END
33 GO

 1 DECLARE @tintLoopID AS TINYINT;
 2 SET @tintLoopID = 1;
 3  
 4 DECLARE @dtmDate AS DATETIME;
 5 SET @dtmDate = '2016-01-07';
 6  
 7 SELECT @dtmDate AS 'date', DATENAME(WEEKDAY, @dtmDate) AS 'WeekdayName';
 8 WHILE @tintLoopID <= 7
 9 BEGIN
10     SET DATEFIRST @tintLoopID;
11  
12     SELECT @@DATEFIRST AS 'Start Day Of Week[1=星期一、2=星期二、3=星期三,……，7=星期日]', dbo.ufn_WeekdayOfWeek(@dtmDate) AS 'Nth Of Week1=星期一、2=星期二、3=星期三,……，7=星期日]'
13  
14     SET @tintLoopID = @tintLoopID + 1; 
15 END
16 GO
17