sqlserver序列可以自定义(sql server 自定义分割月功能详解及实现代码)
sqlserver序列可以自定义
sql server 自定义分割月功能详解及实现代码在最近的项目开发过程中,遇到了Sql server自动分割月的功能需求,这里在网上整理下资料.
1、为何出现自定义分割月的需求
今天梳理一个平台的所有函数时,发现了一个自定义分割月函数,也就是指定分割月的开始日索引值(可以从1-31闭区间内的任何一个值)来获取指定日期所对应的分割月数值。这个函数当时是为了解决业务部门获取非标准月(标准月就是从每个月的第一天到最后一天组成一个完成的标准月份)的统计汇总数据的。例如:如果指定分割月的开始日索引值为5则表示某个月的5号到下个月的4号之间作为一个完整的分割月;同样地如果指定分割月的开始日索引值为1则表示标准月等等。
我仔细梳理了这个函数进行了重构简化以及扩展,该自定义分割月函数的实现区别之前写的SQL Server时间粒度系列----第3节旬、月时间粒度详解文章中将一个整数值和月份日期相互转换功能,这个是按照标准月来实现的,虽然思路大致相同,但是并没有针对之前的月份日期和整数值转换函数对来进行扩展而是独立开发新的功能函数。也是为了尽量做到函数功能职责单一性、稳定性、可维护性以及可扩展性。
2、sql server实现自定义分割月功能
自定义分割月功能函数包括两个标量函数:ufn_SegMonths和ufn_SegMonth2Date。ufn_SegMonths获取指定的日期在自定义分割月对应的分割月数值;ufn_SegMonth2Date获取指定一个分割月数值赌对应的月份日期。
sql server 版本的实现T-SQL代码如下:
IF OBJECT_ID(N'[dbo].[ufn_SegMonths]', 'FN') IS NOT NULL BEGIN DROP FUNCTION [dbo].[ufn_SegMonths]; END GO --================================== -- 功能:根据自定义月开始索引值获取指定日期所在的自定义月数。 -- 说明:自定义分割月数 = 年整数值*100 + 当前所在分割月值。 -- 环境:SQL Server 2005+。 -- 调用:SET @intSegMonths = dbo.fn_SegMonths('2008-01-14', 15)。 -- 创建:XXXX-XX-XX XX:XX-XX:XX XXX 创建函数实现。 -- 修改:XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。 --================================== CREATE FUNCTION [dbo].[ufn_SegMonths] ( @dtmDate AS DATETIME -- 日期 ,@tntSegStartIndexOfMonth AS INT = 15 -- 自定义分割月开始索引值(1-31) ) RETURNS INT AS BEGIN IF (@tntSegStartIndexOfMonth = 0 OR @tntSegStartIndexOfMonth >= 32) BEGIN SET @tntSegStartIndexOfMonth = 15; END DECLARE @intYears AS INT ,@tntMonth AS TINYINT ,@sntDay AS SMALLINT; SELECT @intYears = DATEDIFF(YEAR, '1900-01-01', @dtmDate) ,@tntMonth = DATEPART(MONTH, @dtmDate) ,@sntDay = DATEPART(DAY, @dtmDate); IF (@sntDay >= @tntSegStartIndexOfMonth) BEGIN SET @tntMonth = @tntMonth + 1; END IF (@tntMonth > 12) BEGIN SELECT @intYears = @intYears + 1 ,@tntMonth = @tntMonth - 12; END RETURN @intYears * 100 + @tntMonth; END GO IF OBJECT_ID(N'[dbo].[ufn_SegMonths2Date]', 'FN') IS NOT NULL BEGIN DROP FUNCTION [dbo].[ufn_SegMonths2Date]; END GO --================================== -- 功能:获取自定义分割月数对应的自定义分割月日期。 -- 说明:自定义分割月日期 = 自定义分割月数/100对应的年整数日期“组合”当前所在分割月值。 -- 环境:SQL Server 2005+。 -- 调用:SET @dtmSegMonthDate = dbo.fn_SegMonths2Date(11602)。 -- 创建:XXXX-XX-XX XX:XX-XX:XX XXX 创建函数实现。 -- 修改:XXXX-XX-XX XX:XX-XX:XX XXX XXXXXXXX。; --================================== CREATE FUNCTION [dbo].[ufn_SegMonths2Date] ( @intSegMonths AS INT -- 自定义分割月数 ) RETURNS DATETIME AS BEGIN DECLARE @dtmDefaultBasedate AS DATETIME; SET @dtmDefaultBasedate = '1900-01-01'; IF ((@intSegMonths IS NULL) OR (@intSegMonths <= 0)) BEGIN RETURN @dtmDefaultBasedate; END DECLARE @intYears AS INT ,@intMonth AS INT; SELECT @intYears = @intSegMonths / 100 ,@intMonth = @intSegMonths % 100; RETURN DATEADD(MONTH, @intMonth - 1, DATEADD(YEAR, @intYears, @dtmDefaultBasedate)); END GO
3、测试验证效果
针对以上简单的测试代码如下:
DECLARE @dtmStartDate AS DATETIME ,@dtmEndDate AS DATETIME; SELECT @dtmStartDate = '2000-01-01' ,@dtmEndDate = '2016-12-31'; SELECT [T1].* ,[dbo].[ufn_SegMonths2Date]([T1].[SegMonths]) AS SegMonthDate FROM ( SELECT [T].[CDate] ,[dbo].[ufn_SegMonths]([T].[CDate], 28) AS SegMonths FROM ( SELECT DATEADD(DAY, [Num], @dtmStartDate) AS CDate FROM [dbo].[ufn_GetNums](0, DATEDIFF(DAY, @dtmStartDate, @dtmEndDate)) ) AS T WHERE [T].[CDate] BETWEEN '2014-12-01' AND '2016-03-31' ) AS T1 WHERE DATEPART(DAY, [T1].[CDate]) >= 27 GO
效果截图如下:
注意:以上测试代码使用了SQL Server数字辅助表的实现这边文章的内联表值函数ufn_GetNums。
4、总结语
这次是梳理平台的功能性函数所进行的重构简化以及扩展的实现。尽量将日期有关的功能函数梳理出来,便于直接在sql server用户数据库中来使用, 也便于BI仓库中使用。国庆一来已经过去一周,原来打算一周一遍的计划还是延期啦,再次严重检讨自己。
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
- SQLserver中cube:多维数据集实例详解(SQLserver中cube:多维数据集实例详解)
- sqlserver2014怎么重新激活(解决Windows 10家庭版安装SQL Server 2014出现.net 3.5失败问题)
- SqlServer 按时间段查询问题(SqlServer 按时间段查询问题)
- sqlserver2000显示无服务器(SQL SERVER 2000 9003错误的解决方法只适用于SQL2000)
- 修改sqlserver数据库名称步骤(SQL server数据库创建代码 filegroup文件组修改的示例代码)
- sqlserver去除空格函数(sqlServer实现去除字符串空格)
- SQLServer将数据导出为SQL脚本
- sqlserver自增长id重置(SQL server 自增ID--序号自动增加的字段操作)
- sqlserver最大数据导入量(SQL Server批量插入数据案例详解)
- SQLServer中防止并发插入重复数据的方法详解(SQLServer中防止并发插入重复数据的方法详解)
- sqlserver备份整表数据的语句(SqlServer批量备份多个数据库且删除3天前的备份)
- sqlserver2008数据库的备份(sql server 2008 压缩备份数据库20g)
- sqlserver如何可视化表(sql server使用公用表表达式CTE通过递归方式编写通用函数自动生成连续数字和日)
- sqlserver基础查询语句(SQL Server子查询的深入理解)
- SqlServer2016模糊匹配的三种方式及效率问题简析(SqlServer2016模糊匹配的三种方式及效率问题简析)
- SQLServer设置客户端使用IP地址登录的图文详解(SQLServer设置客户端使用IP地址登录的图文详解)
- 开国中将,王牌军63军首任政委,两个连襟一个上将一个少将传为佳话(王牌军63军首任政委)
- 臭名昭著的731部队最高负责人 石井四郎(臭名昭著的731部队最高负责人)
- 王牌部队,你看的剧情我看的时尚(你看的剧情我看的时尚)
- 被鉴定的古董价值300万 当心,你可能遇到诈骗了(被鉴定的古董价值300万)
- 英语难学吗(初中英语难学吗)
- 如何追女孩子(如何追女孩子的技巧和方法)
热门推荐
- div内容水平垂直居中的方法(不定宽高的文字在div中垂直居中实现方法)
- python使用telnet(python 处理telnet返回的More,以及get想要的那个参数方法)
- java怎么调用支付宝的扫码支付(python实现银联支付和支付宝支付接入)
- jquerycss3轮播案例(CSS3实现列表无限滚动/轮播效果)
- dedecms时间代码(Dedecms中常用数据调用的sql语句汇总)
- dedecms中的有些功能如何修改(DEDECMS 扩展标签和dede自定义标签实现方法)
- css的字体大全(CSS 常用中文字体 Unicode 编码表)
- 是选择vps还是云主机(云主机和VPS主机的不同之处)
- ILSpy反编译工具的使用
- mysql中date_format日期格式化
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9