SQL Server时间粒度系列----第3节旬、月时间粒度详解

本文目录列表：

1、SQL Server旬时间粒度
2、SQL Server月有关时间粒度

SQL Server旬时间粒度

SQL Server不存在旬这个时间粒度，这个时间粒度在我们国家存在的。一个月分为上、中、下3旬，上旬和中旬均10天，分别对应第1天到第10天和第11天到第20天，下旬有可能8天、9天、10天和11天，从第21天到月末最后1天。从旬的定义得知旬是个日期时间范围的，那么如何实现将旬时间粒度时间值和一个对应的整数来实现相互转换呢？从旬的定义得知每旬都有个开始日期的，这个日期我们就叫做旬基准日期。如果同一旬的任意一个日期都对应一个相同的整数，那么再将一个整数转换为一个旬基准日期，这样我们可以实现旬基准日期和整数的相互转换。我们还可以得到每个日期在所在旬的第几天，也可以将得到一个月份每日期所在的旬索引（这个类似每周的任何一天对应的weekday值，从1到7分别对应星期一到星期日）。

提供获得指定日期在当前月哪个旬的功能的函数，T-SQL代码如下：

 IF OBJECT_ID(N'dbo.ufn_PeriodOfMonth', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_PeriodOfMonth;

 END

 GO

 --==================================

 -- 功能: 获得指定日期时间在当前月的旬索引

 -- 说明: 从1开始计数，、、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、、、……、、、、

     DECLARE @tintDayOfMonth AS TINYINT;

     SET @tintDayOfMonth = DAY(@dtmDate);

     -- 旬偏移索引，:上旬，:中旬，:下旬

     DECLARE @tintPeriodOffsetIndexID AS INT;

     SET @tintPeriodOffsetIndexID = DAY(@dtmDate) / 

     IF @tintDayOfMonth IN (, , , )

     BEGIN

         SET @tintPeriodOffsetIndexID = @tintPeriodOffsetIndexID - ;

     END

     RETURN @tintPeriodOffsetIndexID + ;

 END

 GO

提供获得指定日期在当前旬第几天的功能函数，T-SQL代码如下：

 IF OBJECT_ID(N'dbo.ufn_DayOfPeriod', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_DayOfPeriod;

 END

 GO

 --==================================

 -- 功能: 获得指定日期时间在当前旬的日索引

 -- 说明: 从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;

 END

 GO

提供旬基准日期和整数相互转换的功能函数，T-SQL代码如下：

 IF OBJECT_ID(N'dbo.ufn_Periods', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_Periods;

 END

 GO

 --==================================

 -- 功能: 获得指定日期时间基于基准日期的总旬数(一个整数值）

 -- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期；

 --       结果值为非负整数，从0开始计数。

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @intPeriods = dbo.ufn_Periods('2008-01-14')

 --==================================

 CREATE FUNCTION [dbo].[ufn_Periods]

 (

     @dtmDate DATETIME            -- 指定的日期时间

 ) RETURNS INT

     --$Encode$--

 AS

 BEGIN

     SET @dtmDate = dbo.ufn_GetValidDate(@dtmDate);

     -- 旬偏移索引ID,0:上旬，1:中旬，2:下旬

     DECLARE @tintPeriodOffsetIndexID AS INT;

     SET @tintPeriodOffsetIndexID = dbo.ufn_PeriodOfMonth(@dtmDate);

     -- datepart参数也可以为mm或m

     RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate) * 3 + @tintPeriodOffsetIndexID;

 END

 GO

 IF OBJECT_ID(N'dbo.ufn_Periods2Date', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_Periods2Date;

 END

 GO

 --==================================

 -- 功能: 获得一个整数值基于基准日期对应的旬基准日期

 -- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；

 --       如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；

 --       结果值为从基准日期开始计数的日期；

 --       旬基准日期是指一个月份中第1天、第11天和第21天对应的日期，比如'2016-02'月份的3个旬基准日期分别为'2016-02-01','2016-02-11','2016-02-21'。

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @dtmDate = dbo.ufn_Periods2Date(2705) --'1975-02-21'

 --==================================

 Create FUNCTION dbo.ufn_Periods2Date

 (

     @intPeriods INT

 ) RETURNS DATETIME

 AS

 BEGIN

     SET @intPeriods = dbo.ufn_GetValidDateNum(@intPeriods);

     DECLARE @intMaxPeriods AS INT;

     SET @intMaxPeriods = dbo.ufn_Periods('9999-12-31');

     IF @intPeriods >= @intMaxPeriods

     BEGIN

         SET @intPeriods = @intMaxPeriods;

     END

     -- datepart参数也可以为mm或m

     -- 以下注释的也可以，不过更接近相对日期+偏移数得到旬基准日期。

     --RETURN DATEADD(MONTH, @intPeriods / 3, '1900-01-01') + (@intPeriods - @intPeriods / 3 * 3) * 10;

     RETURN DATEADD(DAY,  (@intPeriods - @intPeriods / 3 * 3) * 10, DATEADD(MONTH, @intPeriods / 3, '1900-01-01'));

 END

 GO

测试以上4个函数效果的T-SQL代码如下：

 DECLARE @dtmDate AS DATETIME;

 SET @dtmDate = '2016-01-08';

 SELECT dbo.ufn_PeriodOfMonth(@dtmDate) AS 'Period IndexID Of Current Month(1:上旬，2:中旬，3:下旬)'

     ,dbo.ufn_DayOfPeriod(@dtmDate) AS 'Day IndexID Of Current Period(从1开始结束，包括1、2、3、……、10、11)';

 SELECT dbo.ufn_Periods('1900-02-01') AS 'The Total Of Periods Base-on BaseDate"1900-01-01" '

     ,dbo.ufn_Periods2Date(1) AS 'Period Basedate Mapping 100'

 GO

执行后的查询结果如下图：

SQL Server时间粒度系列----第3节旬、月时间粒度详解

SQL Server月有关时间粒度

在旬时间粒度小节中，我们引入了旬基准日期的概念，针对月基准日期的定义就是每月的第一天代表的日期时间值，比如”2016-01-08“所在的月基准日期就是”2016-01-01“。我们也提供月基准日期和整数相互转换的功能函数。指定日期的月份在当前季度中的月索引，从1开始计数，包括1、2、3。SQL Server提供了month(@dtmDateTime）或datepart(month, @dtmDateTime)获取指定日期所在月份（当前年的份索引，从1开始计数，包括1,、2、3、……、10、11、12）。

提供获得指定日期的月份在当前季度的月索引，T_SQL代码如下：

 IF OBJECT_ID(N'dbo.ufn_MonthOfQuarter', N'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_MonthOfQuarter;

 END

 GO

 --==================================

 -- 功能: 获得一个日期时间的月份在当前季度的月索引

 -- 说明: 如果指定的整数值为NULL时，则其值默认设置为基准日期'1900-01-01'；

 --       结果值从1开始计数，包括1、2、3。

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @dtmDate = dbo.ufn_MonthOfQuarter(2008-01-01);

 --==================================

 CREATE FUNCTION dbo.ufn_MonthOfQuarter

 (

     @dtmDate DATETIME            -- 指定的日期时间

 ) RETURNS TINYINT

 AS

 BEGIN

     SET @dtmDate = ISNULL(@dtmDate, '1900-01-01');

     DECLARE @tintMonthOfQuarter AS TINYINT;

     SET @tintMonthOfQuarter = 0;

     DECLARE @tintMonthOfYear AS TINYINT;

     SET @tintMonthOfYear = MONTH(@dtmDate);

     SET @tintMonthOfQuarter =  @tintMonthOfYear - @tintMonthOfYear / 3 * 3;

     IF @tintMonthOfQuarter = 0

     BEGIN

         SET @tintMonthOfQuarter = 3;

     END

     RETURN @tintMonthOfQuarter;

 END

 GO

提供月基准日期和整数相互转换的功能函数，T-SQL代码入下：

 IF OBJECT_ID(N'dbo.ufn_Months', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_Months;

 END

 GO

 --==================================

 -- 功能: 获得指定日期时间基于基准日期的总月数(一个整数值）

 -- 说明: 如果指定的日期时间为NULL或者小于基准日期“1900-01-01”时，则其值默认基准日期

 --       结果值为非负整数，从0开始计数。

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @intMonths = dbo.ufn_Months('2008-01-14')

 --==================================

 CREATE FUNCTION dbo.ufn_Months

 (

     @dtmDate DATETIME

 ) RETURNS INT

 AS

 BEGIN

     IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'

     BEGIN

         SET @dtmDate = '1900-01-01';

     END

     -- datepart参数也可以为mm或m

     RETURN DATEDIFF(MONTH, '1900-01-01', @dtmDate);

 END

 GO

 IF OBJECT_ID(N'dbo.ufn_Months2Date', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_Months2Date;

 END

 GO

 --==================================

 -- 功能: 获得一个整数值基于基准日期对应的月基准日期

 -- 说明: 如果指定的整数值为NULL或为负整数时，则其值默认为0；

 --       如果指定的整数值大于“9999-12-31”对应的整数值时，则其值默认设置为“9999-12-31”对应的整数值；

 --       结果值为从基准日期开始计数的日期；

 --       月基准日期是指一个月份中第1天对应的日期，比如'2016-02'月份的月旬基准日期为'2016-02-01'。

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @dtmDate = dbo.ufn_Months2Date(1296) --'2008-01-01'

 --==================================

 CREATE FUNCTION dbo.ufn_Months2Date

 (

     @intMonths INT

 ) RETURNS DATETIME

 AS

 BEGIN

     IF @intMonths IS NULL OR @intMonths < 0

     BEGIN

         SET @intMonths = 0;

     END

     DECLARE @intMaxMonths AS INT;

     SET @intMaxMonths = dbo.ufn_Months('9999-12-31');

     IF @intMonths >= @intMaxMonths

     BEGIN

         SET @intMonths = @intMaxMonths;

     END

     -- datepart参数也可以为mm或m

     RETURN DATEADD(MONTH, @intMonths, '1900-01-01');

 END

 GO

测试有关月时间粒度的函数效果的T-SQL代码如下：

 DECLARE @dtmDate AS DATETIME;

 SET @dtmDate = '2016-01-08';

 SELECT dbo.ufn_MonthOfQuarter(@dtmDate) AS 'Month IndexID Of Current Quarter(1:第1个月，2:第2个月，3:第3个月)'

     ,MONTH(@dtmDate) AS 'Month IndexID Of Current Year', DATEPART(MONTH, @dtmDate) AS 'Month IndexID Of Current Year（从1开始结束，包括1、2、3、……、10、12)）';

 SELECT dbo.ufn_Months(@dtmDate) AS 'The Total Of Months Base-on BaseDate"1900-01-01"'

     ,dbo.ufn_Months2Date(2) AS 'Month Basedate Mapping 2';

 GO

执行后的查询结果如下图：

SQL Server函数重构

在旬和月时间粒度已经提供的功能函数，我们会针对指定的日期时间和整数参数做进行相应的条件处理，发现了很多的重复T-SQL代码，为消除相似功能的重复代码，我将其封装在以下两个功能函数ufn_GetVlidDate和ufn_GetValidDateNum，分别提供对指定的日期时间和指定的日期数的有效性处理。

提供以上功能函数的重构，T-SQL代码如下：

 IF OBJECT_ID(N'dbo.ufn_GetValidDate', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_GetValidDate;

 END

 GO

 --==================================

 -- 功能: 获取有效日期

 -- 说明: 指定的日期如果为NULL或者小于基准日期'1900-01-01'则默认设置为基准日期'1900-00-01'

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @dtmDate = dbo.ufn_GetValidDate('2008-01-14')

 --==================================

 CREATE FUNCTION dbo.ufn_GetValidDate

 (

     @dtmDate DATETIME            -- 指定的日期数

 ) RETURNS DATETIME

     --$Encode$--

 BEGIN

     IF @dtmDate IS NULL OR @dtmDate < '1900-01-01'

     BEGIN

         RETURN '1900-01-01';

     END

     RETURN @dtmDate;

 END

 GO

 IF OBJECT_ID(N'dbo.ufn_GetValidDateNum', 'FN') IS NOT NULL

 BEGIN

     DROP FUNCTION dbo.ufn_GetValidDateNum;

 END

 GO

 --==================================

 -- 功能: 获取有效日期数

 -- 说明: 指定的日期数如果为NULL或负数，则默认设置为0

 -- 作者: XXX

 -- 创建: yyyy-MM-dd

 -- 修改: yyyy-MM-dd XXX 修改内容描述

 -- 调用: SET @@intDateNum = dbo.ufn_GetValidDateNum(-1)

 --==================================

 CREATE FUNCTION dbo.ufn_GetValidDateNum

 (

     @intDateNum INT            -- 指定的日期数

 ) RETURNS INT

     --$Encode$--

 BEGIN

     IF @intDateNum IS NULL OR @intDateNum < 0

     BEGIN

         RETURN 0;

     END

     RETURN @intDateNum;

 END

 GO

测试以上功能函数的T-SQL代码如下：

 SELECT dbo.ufn_GetValidDate(NULL) AS 'NULL Default BaseDate"1900-01-01"', dbo.ufn_GetValidDate('1899-12-31') AS 'Less Than BaseDate"1900-01-01" Default BaseDate"1900-01-01"', dbo.ufn_GetValidDate('2016-01-08') AS '2016-01-08'

 SELECT dbo.ufn_GetValidDateNum(NULL) AS 'NULL Defult 0', dbo.ufn_GetValidDateNum(-1) AS 'Less Than 0 Defult 0', dbo.ufn_GetValidDateNum(12) AS ''

 GO

执行后的查询结果如下图：

注意：重复的代码实现了重构，本文中提供的重复代码所在的函数没有做变更的，需要的话请自己调整和修改代码。

测试日期时间与加减运算符的效果，T-SQL代码如下：

 DECLARE @dtmDate AS DATETIME;

 SET @dtmDate = '';

 SELECT @dtmDate AS '"" Default BaseDate"1900-01-01"';

 SET @dtmDate = 0;

 SELECT @dtmDate AS '0 Default BaseDate"1900-01-01"';

 SET @dtmDate = @dtmDate + 1;

 SELECT @dtmDate AS 'Add operator 等同dateadd(day, num, date)';

 SET @dtmDate  = -1;

 SELECT @dtmDate AS 'substract operator 等同dateadd(day, -1*num, date)';

 GO

执行后的查询结果如下图：

注意：以上的测试代码，SQL Server引擎默认使用了基准日期”1900-01-01“。

总结语

本文在SQL Server时间粒度详解小节中，提供了获取指定日期所在旬的日索引功能和获取指定日期的所在旬在当前月的旬索引，以及旬基准日期和整数相互转换的功能函数。还实现了月基准日期和整数相互转换的功能函数，获取指定日期的月份在当前季度的月索引功能。还提供了T-SQL代码重构的例子，通过封装通用的功能函数来消除重复的T-SQL代码。也提供了日期时间数据类型的变量与加减运算符的测试代码，主要印证SQL Server引擎默认也是在使用基准日期”1900-01-01“。

参考清单列表

1、https://msdn.microsoft.com/zh-cn/library/ms186819(v=sql.90).aspx

2、https://msdn.microsoft.com/zh-cn/library/ms189794(v=sql.90).aspx

SQL Server时间粒度系列----第3节旬、月时间粒度详解的更多相关文章

SQL Server时间粒度系列----第4节季、年时间粒度详解
本文目录列表: 1.SQL Server季时间粒度2.SQL Server年时间粒度 3.总结语 4.参考清单列表 SQL Serve季时间粒度季时间粒度也即是季度时间粒度.一年每3 ...
【目录】sql server 进阶篇系列
随笔分类 - sql server 进阶篇系列 sql server 下载安装标记摘要: SQL Server 2017 的各版本和支持的功能 https://docs.microsoft.com/ ...
SQL Server调优系列基础篇
前言关于SQL Server调优系列是一个庞大的内容体系,非一言两语能够分析清楚,本篇先就在SQL 调优中所最常用的查询计划进行解析,力图做好基础的掌握,夯实基本功!而后再谈谈整体的语句调优. 通过 ...
SQL Server调优系列基础篇（常用运算符总结——三种物理连接方式剖析）
前言上一篇我们介绍了如何查看查询计划,本篇将介绍在我们查看的查询计划时的分析技巧,以及几种我们常用的运算符优化技巧,同样侧重基础知识的掌握. 通过本篇可以了解我们平常所写的T-SQL语句,在SQL ...
SQL Server调优系列基础篇（并行运算总结篇二）
前言上一篇文章我们介绍了查看查询计划的并行运行方式. 本篇我们接着分析SQL Server的并行运算. 闲言少叙,直接进入本篇的正题. 技术准备同前几篇一样,基于SQL Server2008R2版 ...
SQL Server调优系列基础篇（索引运算总结）
前言上几篇文章我们介绍了如何查看查询计划.常用运算符的介绍.并行运算的方式,有兴趣的可以点击查看. 本篇将分析在SQL Server中,如何利用先有索引项进行查询性能优化,通过了解这些索引项的应用方 ...
SQL Server调优系列进阶篇（查询语句运行几个指标值监测）
前言上一篇我们分析了查询优化器的工作方式,其中包括:查询优化器的详细运行步骤.筛选条件分析.索引项优化等信息. 本篇我们分析在我们运行的过程中几个关键指标值的检测. 通过这些指标值来分析语句的运行问 ...
SQL Server调优系列进阶篇（深入剖析统计信息）
前言经过前几篇的分析,其实大体已经初窥到SQL Server统计信息的重要性了,所以本篇就要祭出这个神器了. 该篇内容会很长,坐好板凳,瓜子零食之类... 不废话,进正题技术准备数据库版本为SQ ...
SQL Server调优系列进阶篇（如何维护数据库索引）
前言上一篇我们研究了如何利用索引在数据库里面调优,简要的介绍了索引的原理,更重要的分析了如何选择索引以及索引的利弊项,有兴趣的可以点击查看. 本篇延续上一篇的内容,继续分析索引这块,侧重索引项的日常 ...

随机推荐

C&num;&colon;只运行一个程序
一.通过系统事件 1.实现如下: using System; using System.Collections.Generic; using System.Linq; using System.Tex ...
Python数据结构——栈、队列的实现(二)
1. 一个列表实现两个栈 class Twostacks(object): def __init__(self): self.stack=[] self.a_size=0 self.b_size=0 ...
用 Python脚本生成 Android SALT 扰码
发布Android 有偿应用时需要随机生成 SALT 扰码夹在文件中,以下是 Python脚本(当然你选择 C/Java/SHELL/Perl 或别的都行) #!/usr/bin/python # F ...
ubuntu rpm 包安装使用
ubuntu安装rpm的方法RED HAT系列用rpm包安装软件,Debian系列得用deb包.毕竟red hat历史悠久,有很多rpm软件包还未对应deb格式.(对应的地方现在是越来越多)在网上找了 ...
block存储区域——怎样验证block在栈上，还是堆上
Block存储区域首先,须要引入三个名词: ● _NSConcretStackBlock ● _NSConcretGlobalBlock ● _NSConcretMallocBlock 正如它们名字 ...
docker的网络-Container network interface(CNI)与Container network model(CNM)
Overview 目前围绕着docker的网络,目前有两种比较主流的声音,docker主导的Container network model(CNM)和社区主导的Container network in ...
限制input[type=number]的输入位数策略整理
当我们使用类型number的input输入框的时候,我们可能需要限制输入的位数,这个时候通常会想到maxlength,但是maxlength是在number类型的时候是不支持的,下面是一些解决这种问题 ...
eclipse环境下，java操作MySQL的简单演示
首先先通过power shell 进入MySQL 查看现在数据库的状态(博主是win10系统) 右键开始,选择Windows powershell ,输入MySQL -u用户名 -p密码选择数据库( ...
maven的安装教程
一.准备工作 1.确定电脑上已经成功安装jdk7.0以上版本 2.win10操作系统 3.maven安装包下载 ...
大战Java虚拟机【3】—— 类加载机制
前言当你的代码编译成class文件之后,那么虚拟机如何加载这些文件呢?我们需要知道虚拟机到底做了什么样的事情. 类的生命周期加载--链接---初始化----使用---卸载类加载过程 1.加载读 ...