数据库技巧—SQL Server日期函数的巧妙用法

最新推荐文章于 2022-03-10 18:03:55 发布
最新推荐文章于 2022-03-10 18:03:55 发布
阅读量939 收藏
点赞数
分类专栏： 数据库 文章标签： sql server date 工作 数据库 存储



 

对于开发人员来说，日期处理或许简单，或许很难。结合自己过往的开发经验并整合网上的例子，总结出一些日期相关的操作，供自己备用及为大家分享：
 
注意：所有系统日期和时间值均得自运行 SQL Server 实例的计算机的操作系统。
每一种日期类型有其使用范围，当然以刚好适用为最佳选择，切记不要为了方便，什么都用datetime类型。从性能方面会有很大影响，举个例子：
一个表，有1亿行的数据，有10列日期型（对于一些历史表来说这是完全有可能的）。如果全部使用datetime，那么光这部分的存储空间就是：10*100000000*8字节/(1024*1024)≈7629M≈7.4G,当然，如果有这样的需要，再大也还是要用的，假设其实业务上不需要那么精确（因为datetime是精确到0.00333秒），只需要精确到1分钟即可，那么毫不犹豫使用smalldatetime，可以减少一半的空间，也就是大约3.7G。减少空间的好处有很多，比如备份及数据库文件的大小可以减少，让有限的预算做更多的事情。而且数据页固定8KB，越少的体积单页能存放的数据也就越多，查询时要访问的页面就更少，缓解I/O压力。同时对索引的使用也更有效，等等。
所以这里就能体现出“设计”的重要性。

常用日期处理案例：这是文章的重点，因为上面大部分内容都可以从联机丛书中查到
给定某个日期，计算相关的值，目前我的工作中遇到比较多的就是这些，至于有些特殊历法所需日期，目前没遇到，所以没总结：
--定义给定的一天
DECLARE @Date DATETIME = GETDATE();
 
SELECT @Date AS '目前时间'
,DATEADD(DD,-1,@Date) AS '前一天'
,DATEADD(DD,1,@Date) AS '后一天'
/*月计算*/
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0) AS '月初'--在SQL Server中0 代表1900-01-01,通过月运算，保证日恒久为1号
,DATEADD(DD,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到天)'--找到下月初再扣减1天，建议使用DATEADD而不要直接“-1”
,DATEADD(SS,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)) AS '月末(精确到datetime的小数位)'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0) AS '上月第一天'
,DATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)) AS '上月最后一天'
,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0) AS '下月第一天'
,DATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))) AS '下月最后一天'
/*周计算*/
,DATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date) AS '本周第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1) AS '所在星期的星期日'--注意此处与@@datefirst的值有关
,DATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date) AS '所在星期的第二天'--注意此处与@@datefirst的值有关,其他天数类推
,DATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '上个星期第一天(周日)'--注意此处与@@datefirst的值有关
,DATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date)) AS '下个星期第一天(星期日)'--注意此处与@@datefirst的值有关
,DATENAME(WEEKDAY,@Date) AS '本日是周几'
,DATEPART(WEEKDAY,@Date) AS '本日是周几'--返回值 1-星期日，2-星期一，3-星期二......7-星期六
/*年度计算*/
,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0) AS '年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1) AS '年末'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0) AS '去年年初'
,DATEADD(YEAR,DATEDIFF(YEAR,-0,@Date),-1) AS '去年年末'
,DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0) AS '明年年初'
,DATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1) AS '明年年末'
/*季度计算*/
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0) AS '本季季初'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1) AS '本季季末'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0) AS '上季季初'
,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1) AS '上季季末'
,DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0) AS '下季季初'
,DATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1) AS '下季季末'

建议：
创建时间维度表：在本人以前工作中，经常需要查询时间范围（精确到天），此时，可以创建一个表，每一行对应一天，然后其他列就是所需日期，比如季初季末、月初月末、年初年末甚至上年下年等。以供直接调用，并且就算存10年的数据，也就3000多条。有这样需求的可以考虑使用。
 
速查手册：日期往往要转换成字符型再进行处理，所以这里贴出部分转换结果
Select CONVERT(varchar(100), GETDATE(), 0)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1)--05/16/06
Select CONVERT(varchar(100), GETDATE(), 2)--06.05.16
Select CONVERT(varchar(100), GETDATE(), 3)--16/05/06
Select CONVERT(varchar(100), GETDATE(), 4)--16.05.06
Select CONVERT(varchar(100), GETDATE(), 5)--16-05-06
Select CONVERT(varchar(100), GETDATE(), 6)--16 05 06
Select CONVERT(varchar(100), GETDATE(), 7)--05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8)--10:57:46
Select CONVERT(varchar(100), GETDATE(), 9)--05 16 200610:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10)--05-16-06
Select CONVERT(varchar(100), GETDATE(), 11)--06/05/16
Select CONVERT(varchar(100), GETDATE(), 12)--060516
Select CONVERT(varchar(100), GETDATE(), 13)--16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14)--10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20)--2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21)--2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22)--05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23)--2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24)--10:57:47
Select CONVERT(varchar(100), GETDATE(), 25)--2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100)--05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101)--05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102)--2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103)--16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104)--16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105)--16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106)--16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107)--05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108)--10:57:49
Select CONVERT(varchar(100), GETDATE(), 109)--05 16 200610:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110)--05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111)--2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112)--20060516
Select CONVERT(varchar(100), GETDATE(), 113)--16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114)--10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120)--2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121)--2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126)--2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130)--18 ???? ?????? 142710:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131)--18/04/142710:57:49:920AM


原文链接：http://topic.csdn.net/u/20120613/01/ac7d8d4f-1aa6-4bf5-aeda-d31eccf33fcb.html

续写经典
关注
专栏目录
SQL Server日期相关知识
God I Must Confess!
06-15 360
对于开发人员来说，日期处理或许简单，或许很难。结合自己过往的开发经验并整合网上的例子，总结出一些日期相关的操作，供自己备用及为大家分享：   注意：所有系统日期和时间值均得自运行 SQL Server 实例的计算机的操作系统。 每一种日期类型有其使用范围，当然以刚好适用为最佳选择，切记不要为了方便，什么都用datetime类型。从性能方面会有很大影响，举个例子： 一个表，有1亿行的数据，有
SQL Server中CONVERT关于日期时间的格式转换
kakak2000的专栏
04-17 1959
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06 Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16 Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06 Select CONVERT(varchar(100), GETD.
SQL中日期函数计算月初/月底/季末/年初/年底
chrisplus的博客
11-28 1万+
SQL SERVER 2005的代码 日期计算，获取月初、月底、上季初、上季末，以及判断是否月底。                 declare @month_bg datetime                 declare @month_ed datetime                 select @month_bg=convert(varchar(8),@trand
mysql上年年末_查询年初，年末，去年年初，明年年初与年末sql语句
weixin_29050513的博客
02-10 3955
查询年初，年末，去年年初，明年年初与年末sql语句查询年初，年末，去年年初，明年年初与年末sql语句--年度计算declare @date datetimeset @date=getdate()--年初，计算给定日期所在年的第一天select dateadd(year,datediff(year,0,@date),0) as '所在年的第一天'--年末，计算给定日期所在年的最后一天select ...
SQL 中 DateTime日期时间及方法函数
zoey的专栏
09-18 1万+
select dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())) /*上个月一号*/ select dateadd(dd,-day(getdate()),getdate()) /* 上月月底 */ select dateadd(dd,-day(getdate())+1,getdate()) /*
50种方法巧妙优化你的SQLServer数据库.doc
04-24
标题中的“50种方法巧妙优化你的SQLServer数据库”指的是针对SQL Server数据库进行性能提升的各种策略和技巧。这些方法旨在改善数据库的响应时间、吞吐量以及资源利用率，以满足日益增长的业务需求。 描述中提到的...
50种方法巧妙优化你的SQL-Server数据库.doc
最新发布
07-15
### 50种方法巧妙优化你的SQL Server数据库 #### 一、引言 数据库性能优化是确保业务系统高效运行的关键之一。特别是在SQL Server环境中，合理的优化策略不仅能显著提升查询速度，还能有效降低服务器资源的消耗。...
SQLServer性能优化--间接实现函数索引或者Hash索引
09-09
总结来说，通过计算列和Hash值，我们可以巧妙地在SQL Server中实现类似函数索引和Hash索引的功能，以适应复杂的查询需求和优化性能。这些方法虽然不是标准的索引类型，但在特定情况下能够提供有效的性能提升，尤其是...
50种方法巧妙优化你的SQL Server数据库
08-22
### 50种方法巧妙优化你的SQL Server数据库 在日常的数据库管理与维护工作中，我们经常会遇到查询速度慢的问题，这不仅影响了系统的性能，也降低了用户体验。本文将基于给出的信息，详细介绍可能导致查询速度变慢的...
50种方法巧妙优化你的SQLServer数据库
08-24
在SQL Server数据库管理中，优化是提升系统性能的关键步骤。以下是一些针对SQL Server数据库的优化策略，旨在提高数据处理速度、减少资源消耗并改善整体系统效率。 1. **I/O设备分离**：将数据、日志和索引分别放置...
sql 将创建时间加8小时，按天统计
LeonTom的博客
04-09 4777
表tb_name中的create_time存的是utc时间，现在业务需求：查要查看东八区，每天创建记录数据 select DATE_FORMAT(CONVERT_TZ(create_time,'+00:00','+08:00'), '%Y-%m-%d') days, count(id)count from tb_namegroup by days; 执行结果： ...
SQL Server时间算法总结
神经网络爱好者
11-08 859
1:  <br /><br />DECLARE<br /><br />@Date<br /><br />  DATETIME    2:  <br /><br />SET<br /><br />@Date<br /><br />=GETDATE()    3:  <br /><br />--前一天，给定日期的前一天    4:  <br /><br />SELECT<br /><br />DATEADD(DAY<br /><br />,-1,@Date<br /><br />) AS<br /><br />
[SQLSERVER]日期字段处理(需求：小时数+8)
zhang666666的博客
01-24 4784
AND CONVERT(nvarchar, u_create_date, 23) BETWEEN ‘2020-01-01’ AND ‘2020-01-31’; //日期转换格式 DATEADD(HOUR, 8, u_create_date) // 返回当前时间 + 8 小时
4.SQL常用函数汇总
zhangke0426的博客
03-10 8394
SQL常用函数
sql 时间函数（全）
热门推荐
weixin_30338461的博客
07-12 3万+
【Cite：http://www.jb51.net/article/20832.htm】 1. 当前系统日期、时间　　select getdate()2. dateadd 在向指定日期加上一段时间的基础上，返回新的 datetime 值　　例如：向日期加上2天　　select dateadd(day,2,'2004-10-15') --返回：2004-10-17 00:00:00....
sql server 选取某段日期某段时间内的数据
个人博客
07-27 1万+
如我要取8月1号到8月30号之间的早上7点半到晚上八点半这段时间内的数据。   SELECT * FROM tableName AS t WHERE t.create_date BETWEEN '2017-08-01 07:30' AND '2017-08-30 20:30' AND CONVERT(CHAR(5), t.create_date, 14) BETWEEN ...
SQLServer 日期函数大全 SQLServer 时间函数大全(转载并完善)
三摩提的博客
10-09 2197
一、统计语句 1、--统计当前【>当天00点以后的数据】 SELECT * FROM 表 WHERE CONVERT(Nvarchar, dateandtime, 111) = CONVERT(Nvarchar, GETDATE(), 111) ORDER BY dateandtime DESC 2、--统计本周 SELECT * FROM 表 WHERE datedi...
sql 分类汇总 列_年底了按月度，年度汇总用只用函数就可轻松解决
weixin_26642481的博客
12-31 733
年末，经常要对一年的数据，进行年度、月度的分类汇总。那么如何只有函数进行汇总呢？我们以下面的销售表为例，先进行月度的汇总，再按照年度进行汇总！步骤：按月度汇总：将光标放置在F2单元格，输入公式：=SUMPRODUCT((YEAR($A$2:$A$278)=F$1)*(MONTH($A$2:$A$278)=$E2)*$C$2:$C$278)此公式的意思是，先用Year函数和Month函数判断A列的销...
SQL Server日期函数详解与常用查询示例
SQL Server中的日期函数是数据库管理中不可或缺的一部分，它们用于处理、比较和格式化日期和时间数据。本文档详细介绍了SQL Server中几种常见的日期函数，以便在查询和分析业务数据时能有效地利用它们。 1. `DATE_...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值