SQL Server时间粒度系列----第7节日历数据表详解
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server时间粒度系列----第7节日历数据表详解，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含35091字，纯文字阅读大概需要51分钟。
内容图文
分钟时间粒度有关函数，T-SQL代码如下：
IF OBJECT_ID(N‘dbo.ufn_Minutes‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_Minutes;
END
GO
--==================================
-- 功能: 获得指定的日期时间基于默认基准日期的总小时数(一个整数值）
-- 说明: 运行在SQL Server 2005 +。
-- 如果指定的日期时间为NULL或者小于默认基准日期时，则其值为默认基准日期。
-- 结果值为非负整数，从0开始计数。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intMinutes = dbo.ufn_Minutes(‘5983-01-24 02:07:00.000‘);
--==================================
CREATE FUNCTION dbo.ufn_Minutes
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS INT
AS
BEGIN
RETURN DATEDIFF(MINUTE, dbo.ufn_GetDefaultBasedate(), dbo.ufn_GetValidDate(@dtmDate));
END
GO
IF OBJECT_ID(N‘dbo.ufn_Minutes2Date‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_Minutes2Date;
END
GO
--==================================
-- 功能: 获得一个整数值基于默认基准日期对应的日期时间
-- 说明: 运行在SQL Server 2005+。
-- 如果指定的整数值为NULL或为负整数时，则其值默认为0。
-- 如果指定的整数值大于“5983-01-24 02:07:00”对应的整数值时，则其值默认设置为“5983-01-24 02:07:00”对应的整数值。
-- 结果值为基于默认基准日期开始计数的日期。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_Minutes2Date(947033) -- ‘2008-01-14 17:00‘
--==================================
CREATE FUNCTION dbo.ufn_Minutes2Date
(
@intMinutes INT -- 指定的整数值
) RETURNS DATETIME
AS
BEGIN
SET @intMinutes = dbo.ufn_GetValidDateNum(@intMinutes);
DECLARE @intMinutesMax AS INT;
SET @intMinutesMax = dbo.ufn_Minutes(‘5983-01-24 02:07:00‘);
IF @intMinutes >= @intMinutesMax
BEGIN
SET @intMinutes = @intMinutesMax;
END
RETURN DATEADD(MINUTE, @intMinutes, dbo.ufn_GetDefaultBasedate());
END
GO
IF OBJECT_ID(N‘dbo.ufn_MinutesOfDay‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_MinutesOfDay;
END
GO
--==================================
-- 功能: 获取指定的日期时间基于所在当前日午夜零时的分钟数
-- 功能: 运行在SQL Server 2005+。
-- 结果值从0开始计数，包括0、1、2、3、1439。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @sintMinutesOfDay = dbo.fn_MinutesOfDay(GETDATE());
--==================================
CREATE FUNCTION dbo.ufn_MinutesOfDay
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS SMALLINT
AS
BEGIN
RETURN DATEPART(HOUR, @dtmDate) * 60 + DATEPART(MINUTE, @dtmDate);
END
GO
-- Test Code
DECLARE @dtmDate AS DATETIME;
SET @dtmDate = ‘2016-02-14 02:14:18‘;
SELECT
@dtmDate AS ‘The Current DateTime‘
,dbo.ufn_Minutes(@dtmDate) AS ‘Minutes‘
,dbo.ufn_Minutes2Date(dbo.ufn_Minutes(@dtmDate)) AS ‘DateTime Mapping Minutes‘
,dbo.ufn_MinutesOfDay(@dtmDate) AS ‘Minutes Of The Current Day‘;
SET @dtmDate = ‘2016-05-20 05:20:48‘;
SELECT
@dtmDate AS ‘The Current DateTime‘
,dbo.ufn_Minutes(@dtmDate) AS ‘Minutes‘
,dbo.ufn_Minutes2Date(dbo.ufn_Minutes(@dtmDate)) AS ‘DateTime Mapping Minutes‘
,dbo.ufn_MinutesOfDay(@dtmDate) AS ‘Minutes Of The Current Day‘;
GO
以上3个函数的测试效果，如下图：
周以上时间粒度基准日期有关函数，T-SQL代码如下：
IF OBJECT_ID(N‘dbo.ufn_FirstDayOfPeriodTable‘, ‘IF‘) IS NOT NULL
BEGIN
DROP FUNCTION ufn_FirstDayOfPeriodTable;
END
GO
--==================================
-- 功能: 旬首日索引表
-- 说明: 运行在SQL Server 2005+。
-- 结果值为表数据，列名为FirstDayOfPeriod，其字段列（域）值包括1、11、21这3个数字，其字段列值对应每年每个旬的第一天。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT * FROM dbo.ufn_FirstDayOfPeriodTable();
--==================================
CREATE FUNCTION dbo.ufn_FirstDayOfPeriodTable
(
) RETURNS TABLE
--$Encode$--
RETURN (
SELECT 1 AS FirstDayOfPeriod
UNION ALL SELECT 11
UNION ALL SELECT 21
);
GO
IF OBJECT_ID(N‘dbo.ufn_FirstDayOfPeriod‘, N‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_FirstDayOfPeriod;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前旬的第一个日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、11、21。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT @tintFirstDayOfPeriod = ufn_FirstDayOfPeriod(@dtmDate);
--==================================
CREATE FUNCTION dbo.ufn_FirstDayOfPeriod
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
BEGIN
DECLARE @tintFirstDayOfPeriod AS TINYINT;
SET @tintFirstDayOfPeriod = 1;
SELECT TOP 1 @tintFirstDayOfPeriod = FirstDayOfPeriod
FROM dbo.ufn_FirstDayOfPeriodTable()
WHERE FirstDayOfPeriod <= DAY(@dtmDate)
ORDER BY FirstDayOfPeriod DESC;
RETURN @tintFirstDayOfPeriod;
END
GO
IF OBJECT_ID(N‘dbo.ufn_BasedateOfPeriod‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_BasedateOfPeriod;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在旬的基准日期
-- 说明: 运行在SQL Server 2005+。
-- 结果值为旬基准日，即每个旬第一天对应的日期。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmBasedateOfPeriod = dbo.ufn_BasedateOfPeriod(‘2016-01-12‘);
--==================================
CREATE FUNCTION dbo.ufn_BasedateOfPeriod
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS DATETIME
--$Encode$--
BEGIN
RETURN CONVERT(DATETIME, CAST(YEAR(@dtmDate) AS CHAR(4)) + ‘-‘ + CAST(MONTH(@dtmDate) AS VARCHAR(2)) + ‘-‘ + CAST(dbo.ufn_FirstDayOfPeriod(@dtmDate) AS VARCHAR(2)), 120);
END
GO
IF OBJECT_ID(N‘dbo.ufn_BasedateOfMonth‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_BasedateOfMonth;
END
GO
--==================================
-- 功能: 获取指定日期时间所在月的基准日期
-- 说明: 运行在SQL Server 2005+。
-- 结果值为月基准日，即每个月第一天对应的日期。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmBasedateOfMonth = dbo.ufn_BasedateOfMonth(‘2016-01-12‘);
--==================================
CREATE FUNCTION dbo.ufn_BasedateOfMonth
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS DATETIME
--$Encode$--
BEGIN
RETURN CONVERT(DATETIME, CAST(YEAR(@dtmDate) AS CHAR(4)) + ‘-‘ + CAST(MONTH(@dtmDate) AS VARCHAR(2)) + ‘-‘ + CAST(1 AS CHAR(1)), 120);
END
GO
IF OBJECT_ID(N‘dbo.ufn_FirstMonthOfQuarterTable‘, ‘IF‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_FirstMonthOfQuarterTable;
END
GO
--==================================
-- 功能: 季首月索引表
-- 说明: 运行在SQL Server 2005+。
-- 结果值为表数据，列名为FirstMonthOfQuarter，其字段列（域）值包括1、4、7、10这4个数字，其字段列值对应每年每个季的第一个月份。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT * FROM dbo.ufn_FirstMonthOfQuarterTable();
--==================================
CREATE FUNCTION dbo.ufn_FirstMonthOfQuarterTable
(
) RETURNS TABLE
--$Encode$--
RETURN (
SELECT 1 AS FirstMonthOfQuarter
UNION ALL SELECT 4
UNION ALL SELECT 7
UNION ALL SELECT 10
);
GO
IF OBJECT_ID(N‘dbo.ufn_FirstMonthOfQuarter‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_FirstMonthOfQuarter;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前季度的第一个月索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、4、7、10。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT @tintFirstMonthOfQuarter = ufn_FirstMonthOfQuarter(@dtmDate);
--==================================
CREATE FUNCTION dbo.ufn_FirstMonthOfQuarter
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
BEGIN
DECLARE @tintFirstMonthOfQuarter AS TINYINT;
SET @tintFirstMonthOfQuarter = 1;
SELECT TOP 1 @tintFirstMonthOfQuarter = FirstMonthOfQuarter
FROM dbo.ufn_FirstMonthOfQuarterTable()
WHERE FirstMonthOfQuarter <= MONTH(@dtmDate)
ORDER BY FirstMonthOfQuarter DESC;
RETURN @tintFirstMonthOfQuarter;
END
GO
IF OBJECT_ID(N‘dbo.ufn_BasedateOfQuarter‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_BasedateOfQuarter;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前季的季基准日期
-- 说明: 运行在SQL Server 2005+。
-- 结果值格式"XXXX-01-01"，包括："XXXX-01-01"、"XXXX-04-01"、"XXXX-07-01"、"XXXX-10-01"。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT @tintFirstMonthOfQuarter = ufn_BasedateOfQuarter(@dtmDate);
--==================================
CREATE FUNCTION dbo.ufn_BasedateOfQuarter
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS DATETIME
--$Encode$--
BEGIN
RETURN CONVERT(DATETIME, CAST(YEAR(@dtmDate) AS CHAR(4)) + ‘-‘ + CAST(dbo.ufn_FirstMonthOfQuarter(@dtmDate) AS VARCHAR(2)) + ‘-‘ + CAST(1 AS CHAR(1)), 120);
END
GO
IF OBJECT_ID(N‘dbo.ufn_BasedateOfYear‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_BasedateOfYear;
END
GO
--==================================
-- 功能: 获取指定日期时间所在年的基准日期
-- 说明: 结果值是每个年的基准日，每个年第一天对应的日期。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmBasedateOfYear = dbo.ufn_BasedateOfYear(‘2016-01-12‘);
--==================================
CREATE FUNCTION dbo.ufn_BasedateOfYear
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS DATETIME
--$Encode$--
BEGIN
RETURN CONVERT(DATETIME, CAST(YEAR(@dtmDate) AS CHAR(4)) + ‘-‘ + CAST(1 AS CHAR(1)) + ‘-‘ + CAST(1 AS char(1)), 120);
END
GO
-- Test Code
DECLARE @dtmDate AS DATETIME;
SET @dtmDate = ‘2016-01-14‘;
SELECT
@dtmDate AS ‘The Current Date‘
,dbo.ufn_BasedateOfPeriod(@dtmDate) AS ‘Basedate Of The Current Period‘
,dbo.ufn_BasedateOfMonth(@dtmDate) AS ‘Basedate Of The Current Month‘
,dbo.ufn_BasedateOfQuarter(@dtmDate) AS ‘Basedate Of the Current Quarter‘
,dbo.ufn_BasedateOfYear(@dtmDate) AS ‘Basedate Of the Current Year‘
,dbo.ufn_GetMinDateTime() AS ‘Basedate Of the Current Date‘;
SET @dtmDate = ‘2016-4-30‘;
SELECT
@dtmDate AS ‘The Current Date‘
,dbo.ufn_BasedateOfPeriod(@dtmDate) AS ‘Basedate Of The Current Period‘
,dbo.ufn_BasedateOfMonth(@dtmDate) AS ‘Basedate Of The Current Month‘
,dbo.ufn_BasedateOfQuarter(@dtmDate) AS ‘Basedate Of the Current Quarter‘
,dbo.ufn_BasedateOfYear(@dtmDate) AS ‘Basedate Of the Current Year‘
,dbo.ufn_GetMinDateTime() AS ‘Basedate Of the Current Date‘;
GO
以上几个函数的测试效果，如下图：
天时间粒度有关函数，T-SQL代码如下：
IF OBJECT_ID(N‘dbo.ufn_Days‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_Days;
END
GO
--==================================
-- 功能: 获得指定日期时间基于默认基准日期的总天数(一个整数值）
-- 说明: 运行在SQL Server 2005+。
-- 如果指定的日期时间为NULL或者小于默认基准日期时，则其值为默认基准日期。
-- 结果值为非负整数，从0开始计数。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intDays = dbo.ufn_Days(‘2008-01-14‘)
--==================================
CREATE FUNCTION dbo.ufn_Days
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS INT
--$Encode$--
AS
BEGIN
RETURN DATEDIFF(DAY, dbo.ufn_GetDefaultBasedate(), dbo.ufn_GetValidDate(@dtmDate));
END
GO
IF OBJECT_ID(N‘dbo.ufn_Days2Date‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_Days2Date;
END
GO
--==================================
-- 功能: 获得一个整数值基于默认基准日期对应的日期
-- 说明: 运行在SQL Server 2005+。
-- 如果指定的整数值为NULL或为负整数时，则其值默认为0。
-- 如果指定的整数值大于最大日期时间对应的整数值时，则其值默认设置为最大日期时间对应的整数值。
-- 结果值为基于默认基准日期开始计数的日期。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_Days2Date(39459) --‘2008-01-14‘
--==================================
CREATE FUNCTION dbo.ufn_Days2Date
(
@intDays INT -- 指定的整数值
) RETURNS DATETIME
--$Encode$--
AS
BEGIN
SET @intDays = dbo.ufn_GetValidDateNum(@intDays);
DECLARE @intMaxDays AS INT;
SET @intMaxDays = dbo.ufn_Days(dbo.ufn_GetMaxDateTime());
IF @intDays >= @intMaxDays
BEGIN
SET @intDays = @intMaxDays;
END
RETURN DATEADD(DAY, @intDays, dbo.ufn_GetDefaultBasedate());
END
GO
IF OBJECT_ID(N‘dbo.ufn_DayOfWeek‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_DayOfWeek;
END
GO
--==================================
-- 功能: 获取指定日期时间的所在当前周的日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1到7，分别对应从周一到周日，该值与@@DATEFISRT配置函数值保持一致。
-- 使用(@@datefirt + datepart(weekday, @dtmDate))%7的结果值从2、3、4、5、6、0、1
-- 分别对应周一、周二、周三、周四、周五、周六、周日。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT dbo.ufn_DayOfWeek(‘2017-01-07‘) -- 4（表示星期四）
--==================================
CREATE FUNCTION dbo.ufn_DayOfWeek
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
BEGIN
DECLARE @tintRemainder AS TINYINT;
SET @tintRemainder = (@@DATEFIRST + DATEPART(WEEKDAY, @dtmDate)) % 7;
IF @tintRemainder <= 1
BEGIN
SET @tintRemainder = @tintRemainder + 6;
END
ELSE
BEGIN
SET @tintRemainder = @tintRemainder - 1;
END
RETURN @tintRemainder;
END
GO
IF OBJECT_ID(N‘dbo.ufn_DayOfPeriod‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_DayOfPeriod;
END
GO
--==================================
-- 功能: 获得指定日期时间在当前旬的日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3、……、8、9、10、11。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intPeriods = dbo.ufn_DayOfPeriod(‘2008-01-14‘)
--==================================
CREATE FUNCTION dbo.ufn_DayOfPeriod
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
AS
BEGIN
--RETURN DAY(@dtmDate) - (dbo.ufn_PeriodOfMonth(@dtmDate) - 1) * 10;
RETURN DATEDIFF(DAY, dbo.ufn_BasedateOfPeriod(@dtmDate), @dtmDate) + 1;
END
GO
IF OBJECT_ID(N‘dbo.ufn_DayOfMonth‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_DayOfMonth;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前月的日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3.……、28、29、30、31。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @sintDayOfYear = dbo.ufn_DayOfMonth(‘2016-01-11‘)
--==================================
CREATE FUNCTION dbo.ufn_DayOfMonth
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
BEGIN
RETURN DAY(@dtmDate);
END
GO
IF OBJECT_ID(N‘dbo.ufn_DayOfQuarter‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_DayOfQuarter;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在季的日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3.……、89、90、91、92。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
--==================================
CREATE FUNCTION dbo.ufn_DayOfQuarter
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
BEGIN
RETURN DATEDIFF(DAY, dbo.ufn_BasedateOfQuarter(@dtmDate), @dtmDate) + 1;
END
GO
IF OBJECT_ID(N‘dbo.ufn_DayOfYear‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_DayOfYear;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前年的日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3.……、364、365、366。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @sintDayOfYear = dbo.ufn_DayOfYear(‘2016-01-11‘)
--==================================
CREATE FUNCTION dbo.ufn_DayOfYear
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS SMALLINT
--$Encode$--
BEGIN
RETURN DATEPART(DAYOFYEAR, @dtmDate);
END
GO
IF OBJECT_ID(N‘dbo.ufn_DayOfDate‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_DayOfDate;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前日期的日索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3.……、3652057、3652058、3652059。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @inntDayOfDate = dbo.ufn_DayOfDate(‘2016-01-11‘)
--==================================
CREATE FUNCTION dbo.ufn_DayOfDate
(
@dtmDate DATETIME
) RETURNS INT
--$Encode$--
BEGIN
RETURN DATEDIFF(DAY, dbo.ufn_GetMinDateTime(), dbo.ufn_GetValidDate(@dtmDate)) + 1
END
GO
-- Test Code
DECLARE @dtmDate AS DATETIME;
SET @dtmDate = ‘2016-01-14‘;
SELECT
@dtmDate AS ‘The Current Date‘
,dbo.ufn_Days(@dtmDate) AS ‘Days‘
,dbo.ufn_Days2Date(dbo.ufn_Days(@dtmDate)) AS ‘Date Mapping Days‘
,dbo.ufn_DayOfWeek(@dtmDate) AS ‘Day Of The Current Week‘
,dbo.ufn_DayOfPeriod(@dtmDate) AS ‘Day Of The Current Period‘
,dbo.ufn_DayOfMonth(@dtmDate) AS ‘Day Of The Current Month‘
,dbo.ufn_DayOfQuarter(@dtmDate) AS ‘Day Of The Current Quarter‘
,dbo.ufn_DayOfYear(@dtmDate) AS ‘Day Of The Current Year‘
,dbo.ufn_DayOfDate(@dtmDate) AS ‘Day Of The Current Date‘;
SET @dtmDate = ‘2016-03-14‘;
SELECT
@dtmDate AS ‘The Current Date‘
,dbo.ufn_Days(@dtmDate) AS ‘Days‘
,dbo.ufn_Days2Date(dbo.ufn_Days(@dtmDate)) AS ‘Date Mapping Days‘
,dbo.ufn_DayOfWeek(@dtmDate) AS ‘Day Of The Current Week‘
,dbo.ufn_DayOfPeriod(@dtmDate) AS ‘Day Of The Current Period‘
,dbo.ufn_DayOfMonth(@dtmDate) AS ‘Day Of The Current Month‘
,dbo.ufn_DayOfQuarter(@dtmDate) AS ‘Day Of The Current Quarter‘
,dbo.ufn_DayOfYear(@dtmDate) AS ‘Day Of The Current Year‘
,dbo.ufn_DayOfDate(@dtmDate) AS ‘Day Of The Current Date‘;
GO
以上几个函数的测试效果，如下图：
周时间粒度有关函数，T-SQL代码如下：
IF OBJECT_ID(N‘dbo.ufn_Weeks‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_Weeks;
END
GO
--==================================
-- 功能: 获得指定日期时间基于默认基准日期的总周数(一个整数值）
-- 说明: 运行在SQL Server 2005+。
-- 如果指定的日期时间为NULL或者小于默认基准日期时，则其值为默认基准日期。
-- 结果值为非负整数，从0开始计数。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intDays = dbo.ufn_Weeks(‘2008-01-14‘)
--==================================
CREATE FUNCTION dbo.ufn_Weeks
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS INT
--$Encode$--
AS
BEGIN
RETURN dbo.ufn_Days(@dtmDate) / 7;
END
GO
IF OBJECT_ID(N‘dbo.ufn_Weeks2Date‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_Weeks2Date;
END
GO
--==================================
-- 功能: 获得一个整数值基于默认基准日期对应的日期
-- 说明: 运行在SQL Server 2005+。
-- 如果指定的整数值为NULL或为负整数时，则其值默认为0；
-- 如果指定的整数值大于最大日期时间对应的整数值时，则其值默认设置为最大日期时间对应的整数值。
-- 结果值为基于默认基准日期开始计数的周基准日期，也就是周一对应的日期。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @dtmDate = dbo.ufn_Weeks2Date(39459);
--==================================
CREATE FUNCTION dbo.ufn_Weeks2Date
(
@intWeeks INT -- 指定的整数值
) RETURNS DATETIME
--$Encode$--
AS
BEGIN
SET @intWeeks = dbo.ufn_GetValidDateNum(@intWeeks);
DECLARE @intMaxWeeks AS INT;
SET @intMaxWeeks = dbo.ufn_Weeks(dbo.ufn_GetMaxDateTime());
IF @intWeeks >= @intMaxWeeks
BEGIN
SET @intWeeks = @intMaxWeeks;
END
RETURN DATEADD(DAY, @intWeeks * 7, dbo.ufn_GetDefaultBasedate());
END
GO
IF OBJECT_ID(N‘dbo.ufn_WeekOfPeriod‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_WeekOfPeriod;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前旬的周索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @tintWeekOfPeriod = dbo.ufn_WeekOfPeriod(‘2016-01-11‘)
--==================================
CREATE FUNCTION dbo.ufn_WeekOfPeriod
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
BEGIN
DECLARE @tintWeekOffsetOfPeriod AS TINYINT;
SET @tintWeekOffsetOfPeriod = DATEDIFF(WEEK, dbo.ufn_BasedateOfPeriod(@dtmDate), @dtmDate);
IF dbo.ufn_DayOfWeek(@dtmDate) = 7 AND @tintWeekOffsetOfPeriod >= 1
BEGIN
SET @tintWeekOffsetOfPeriod = @tintWeekOffsetOfPeriod - 1;
END
RETURN @tintWeekOffsetOfPeriod + 1;
END
GO
IF OBJECT_ID(N‘dbo.ufn_WeekOfMonth‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_WeekOfMonth;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前月的周索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3、4、5
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @tintWeekOfMonth = dbo.ufn_WeekOfMonth(‘2016-01-11‘)
--==================================
CREATE FUNCTION dbo.ufn_WeekOfMonth
(
@dtmDate DATETIME
) RETURNS TINYINT
--$Encode$--
BEGIN
DECLARE @tintWeekOffsetOfMonth AS TINYINT;
SET @tintWeekOffsetOfMonth = DATEDIFF(WEEK, dbo.ufn_BasedateOfMonth(@dtmDate), @dtmDate);
IF dbo.ufn_DayOfWeek(@dtmDate) = 7 AND @tintWeekOffsetOfMonth >= 1
BEGIN
SET @tintWeekOffsetOfMonth = @tintWeekOffsetOfMonth - 1;
END
RETURN @tintWeekOffsetOfMonth + 1;
END
GO
IF OBJECT_ID(N‘dbo.ufn_WeekOfQuarter‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_WeekOfQuarter;
END
GO
--==================================
-- 功能: 获取指定的日期时间所在当前季的周索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，包括1、2、3、4、5、6、7、7、……。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @tintWeekOfQuarter = dbo.ufn_WeekOfQuarter(‘2016-01-11‘)
--==================================
CREATE FUNCTION dbo.ufn_WeekOfQuarter
(
@dtmDate DATETIME
) RETURNS TINYINT
--$Encode$--
BEGIN
DECLARE @tintWeekOffsetOfQuarter AS TINYINT;
SET @tintWeekOffsetOfQuarter = DATEDIFF(WEEK, dbo.ufn_BasedateOfQuarter(@dtmDate), @dtmDate);
IF dbo.ufn_DayOfWeek(@dtmDate) = 7 AND @tintWeekOffsetOfQuarter >= 1
BEGIN
SET @tintWeekOffsetOfQuarter = @tintWeekOffsetOfQuarter - 1;
END
RETURN @tintWeekOffsetOfQuarter + 1;
END
GO
IF OBJECT_ID(N‘dbo.ufn_WeekOfYear‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_WeekOfYear;
END
GO
--==================================
-- 功能: 获取指定日期属于当前年的周索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，一年最多1-53周。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT dbo.ufn_WeekOfYear(‘2016-01-07‘);
--==================================
CREATE FUNCTION dbo.ufn_WeekOfYear
(
@dtmDate DATETIME
) RETURNS TINYINT
--$Encode$--
AS
BEGIN
DECLARE @tintWeekOffsetOfYear AS TINYINT;
SET @tintWeekOffsetOfYear = DATEDIFF(WEEK, dbo.ufn_BasedateOfYear(@dtmDate), @dtmDate);
IF dbo.ufn_DayOfWeek(@dtmDate) = 7 AND @tintWeekOffsetOfYear >= 1
BEGIN
SET @tintWeekOffsetOfYear = @tintWeekOffsetOfYear - 1;
END
RETURN @tintWeekOffsetOfYear + 1;
END
GO
IF OBJECT_ID(N‘dbo.ufn_WeekOfDate‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_WeekOfDate;
END
GO
--==================================
-- 功能: 获取指定日期属于当前日历的周索引
-- 说明: 运行在SQL Server 2008+。
-- 结果值从1开始计数，包括1、2、3、4、……。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SELECT dbo.ufn_WeekOfDate(‘2016-01-07‘);
--==================================
CREATE FUNCTION dbo.ufn_WeekOfDate
(
@dtmDate DATETIME
) RETURNS INT
--$Encode$--
AS
BEGIN
DECLARE @intWeekOffsetOfDate AS INT;
SET @intWeekOffsetOfDate = DATEDIFF(WEEK, dbo.ufn_GetMinDateTime(), @dtmDate);
IF (@@DATEFIRST + DATEPART(WEEKDAY, @dtmDate)) % 7 = 1 AND @intWeekOffsetOfDate >= 1
BEGIN
SET @intWeekOffsetOfDate = @intWeekOffsetOfDate - 1;
END
RETURN @intWeekOffsetOfDate + 1;
END
GO
-- Test Code
DECLARE @dtmDate AS DATETIME;
SET @dtmDate = ‘2016-01-15‘;
SELECT
@dtmDate AS ‘The Current Date‘
,dbo.ufn_Weeks(@dtmDate) AS ‘Weeks‘
,dbo.ufn_Weeks2Date(dbo.ufn_Weeks(@dtmDate)) AS ‘Date Mapping Weeks‘
,dbo.ufn_WeekOfPeriod(@dtmDate) AS ‘Week Of The Current Period‘
,dbo.ufn_WeekOfMonth(@dtmDate) AS ‘Week Of The Current Month‘
,dbo.ufn_WeekOfQuarter(@dtmDate) AS ‘Week Of The Current Quarter‘
,dbo.ufn_WeekOfYear(@dtmDate) AS ‘Week Of The Current Year‘
,dbo.ufn_WeekOfDate(@dtmDate) AS ‘Week Of The Current Date‘;
SET @dtmDate = ‘2016-01-31‘;
SELECT
@dtmDate AS ‘The Current Date‘
,dbo.ufn_Weeks(@dtmDate) AS ‘Weeks‘
,dbo.ufn_Weeks2Date(dbo.ufn_Weeks(@dtmDate)) AS ‘Date Mapping Weeks‘
,dbo.ufn_WeekOfPeriod(@dtmDate) AS ‘Week Of The Current Period‘
,dbo.ufn_WeekOfMonth(@dtmDate) AS ‘Week Of The Current Month‘
,dbo.ufn_WeekOfQuarter(@dtmDate) AS ‘Week Of The Current Quarter‘
,dbo.ufn_WeekOfYear(@dtmDate) AS ‘Week Of The Current Year‘
,dbo.ufn_WeekOfDate(@dtmDate) AS ‘Week Of The Current Date‘;
GO
测试以上函数的效果，如下图：
旬时间粒度有关函数，T-SQL代码如下：
IF OBJECT_ID(N‘dbo.ufn_PeriodOfMonth‘, ‘FN‘) IS NOT NULL
BEGIN
DROP FUNCTION dbo.ufn_PeriodOfMonth;
END
GO
--==================================
-- 功能: 获得指定日期时间在当前月的旬索引
-- 说明: 运行在SQL Server 2005+。
-- 结果值从1开始计数，1、2、3分别对应上、中、下的旬索引。
-- 作者: XXX
-- 创建: yyyy-MM-dd
-- 修改: yyyy-MM-dd XXX 修改内容描述
-- 调用: SET @intPeriods = dbo.ufn_PeriodOfMonth(‘2008-01-14‘)
--==================================
CREATE FUNCTION [dbo].[ufn_PeriodOfMonth]
(
@dtmDate DATETIME -- 指定的日期时间
) RETURNS TINYINT
--$Encode$--
AS
BEGIN
--当前月的日索引，从1开始计数，，包括1、2、3、……、28、29、30、31
DECLARE @tintDayOfMonth AS TINYINT;
&n
本文系统来源：http://www.cnblogs.com/dzy863/p/5133333.html
内容总结
以上是互联网集市为您收集整理的SQL Server时间粒度系列----第7节日历数据表详解全部内容，希望文章能够帮你解决SQL Server时间粒度系列----第7节日历数据表详解所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】