暂无图片
暂无图片
暂无图片
暂无图片
暂无图片
首页 / 常用日期函数太简单？那是你没用过这些高级日期函数（不看后悔系列）

常用日期函数太简单？那是你没用过这些高级日期函数（不看后悔系列）

SQL数据库开发 2020-01-14
623

导读

 
我们在工作中时常需要处理某个时间段的数据，例如：
如何求解上周的销量？
如何求解上月第一天的销售金额？
如何求解去年同期的在线人数？
......
这些都是涉及到具体或者以当前为参照的时间段的数据。我们该如何从海量数据中找出准确的时间段呢？


上次写过一篇介绍高级字符函数的文章，还没看过的小伙伴戳下面：
《常用SQL函数太简单？那是你没用过这些高级函数（不看后悔系列）》

话不多说，今天继续给大家讲解一些比较高级的日期函数。

测试环境
SQL Server 2017

@@DATEFIRST

作用
针对特定会话，此函数返回 SET DATEFIRST 的当前值。

语法
@@DATEFIRST

注意：
SET DATEFIRST n 指定一周的第一天（星期日、星期一、星期二等） 。 n 值的范围为 1 到 7 。

示例
比如我们指定星期天为每周第一天，那么今天（2020-01-13）的

@@DATEFIRST就会返回7。具体如下：


SET DATEFIRST 1; --表示星期一为每周第一天
SELECT @@DATEFIRST; --返回DATEFIRST的值


结果：



这个函数的可能很多人觉得作用不大，其实对设置每周的第一天是很重要的。美国是把每周的星球日当第一天，而我国通常把星期一当为每周的第一天。

比如我想知道上周第3天的销量，你就需要先定义上周的星期几是第1天，才能进一步计算第3天的销量。



系统时间

获取系统时间有一组函数，常见的有GETDATE函数，此外还有这些函数。

SELECT SYSDATETIME()
SELECT SYSDATETIMEOFFSET()
SELECT SYSUTCDATETIME()
SELECT CURRENT_TIMESTAMP  
SELECT GETDATE()
SELECT GETUTCDATE()

结果：
其中包含UTC的系统时间是世界标准时间，其他为当前时区时间。
注意：CURRENT_TIMESTAMP与GETDATE()函数等价。


DATEADD

作用
将指定的 number 值（作为带符号整数）与输入 date 值的指定 datepart 相加，然后返回该修改值 。

语法
DATEADD (datepart , number , date )
其中datepart参数由系统定义，具体参数列表如下：
datepart缩写
年yy, yyyy
季度qq, q
月mm, m
年中的日dy, y
日dd, d
周wk, ww
星期dw, w
小时hh
分钟mi, n
秒ss, s
毫秒ms
微妙mcs
纳秒ns


示例
查询上个月的今天，下个月的今天


SELECT DATEADD(month, -1, '20190113');
SELECT DATEADD(month, 1, '20190113');


结果：


DATEDIFF
作用
返回指定的 startdate 和 enddate 之间所跨的指定 datepart 边界的计数（作为带符号整数值） 。

语法
DATEDIFF ( datepart , startdate , enddate )
注意：返回值为startdate 与 enddate 之间的 int 差异，以 datepart 设置的边界表示 。

示例
计算去年第一天到今天之间有多少天


SELECT DATEDIFF(DAY,'20190101','20200113')


结果：


DATEFROMPARTS

作用
此函数返回映射到指定年、月、日值的 date 值 。

语法
DATEFROMPARTS ( year, month, day )

注意：
  • DATEFROMPARTS 返回一个 date 值，其中日期部分设置为指定的年、月和日，时间部分设置为默认值 。 对于无效参数，DATEFROMPARTS 将引发错误。 如果至少有一个必需参数具有 NULL 值，则 DATEFROMPARTS 返回 NULL。
  • 此函数可在 SQL Server 2012 (11.x) 及更高版本的服务器上执行远程处理。 它不能无法在版本低于 SQL Server 2012 (11.x) 的服务器上执行远程处理。

示例
通过指定具体的年月日，返回今天的日期：
SELECT DATEFROMPARTS ( 2020, 1, 13 ) AS Result;


结果

DATENAME

作用
返回表示指定 date 的指定 datepart 的字符串。

语法
DATENAME ( datepart , date )
示例


SELECT DATENAME(year, getdate()) 'Year' 
    ,DATENAME(month, getdate()) 'Month'
    ,DATENAME(day, getdate()) 'Day'
    ,DATENAME(weekday,getdate()) 'Weekday';


结果：
通过此函数，我们可以知道具体某一天的年月日星期等具体信息。特别是在进行数据分组统计时经常用到。

DATEPART

作用
返回表示指定 date 的指定 datepart 的整数。

语法
DATEPART ( datepart , date )
示例


SELECT DATEPART(year, getdate()) 'Year' 
    ,DATEPART(month, getdate()) 'Month'
    ,DATEPART(day, getdate()) 'Day'
    ,DATEPART(weekday,getdate()) 'Weekday';


结果：

细心的同学可能发现DATEPART和DATENAME有很多相似之处，但是请注意他们返回的类型是不同的，DATEPART返回的是数值类型，DATENAME返回的是字符串类型。

EOMONTH

作用
返回包含指定日期所在月份的最后一天（具有可选偏移量）

语法
EOMONTH ( start_date [, month_to_add ] )

示例
显示本月的最后一天


--定义一个日期类型的变量，并且给它赋值20200113
DECLARE @date DATETIME = '20200113';
SELECT EOMONTH ( @date ) AS Result;
GO


结果：

添加偏移参数month_to_add
例如返回下月的最后一天：


DECLARE @date DATETIME = '20200113';
SELECT EOMONTH ( @date,1 ) AS Result;
GO


结果：

ISDATE
作用
如果表达式是有效的 date、time、或 datetime 值，则返回 1；否则返回 0 。
如果表达式为 datetime2 值，则 ISDATE 返回 0 。

语法
ISDATE ( expression )
示例
判断今天（2020-01-13）是否为合法日期


IF ISDATE('2020-01-30') = 1
    SELECT '合法日期' Result 
ELSE  
    SELECT '不合法日期' Result;


结果：

有兴趣的同学可以尝试判断'2020-02-30'是否合法。

实战应用
1、本月第一天
SELECT   DATEADD(mm,DATEDIFF(mm,0,getdate()),0)
--2020-01-01 00:00:00.000


2、本周星期一
SELECT   DATEADD(wk,DATEDIFF(wk,0,getdate()),0)
--2020-01-13 00:00:00.000


3、今年第一天

SELECT DATEADD(yy,DATEDIFF(yy,0,getdate()),0)
--2020-01-01 00:00:00.000


4、下个季度第一天

SELECT   DATEADD(qq,DATEDIFF(qq,-1,getdate()),0)
--2020-04-01 00:00:00.000


5、上个月最后一天

SELECT DATEADD(dd,-DAY(getdate()),getdate())
--2019-12-31 20:14:21.850


6、今年最后一天

SELECT DATEADD(year,DATEDIFF(year,0,DATEADD(year,1,getdate())),-1)
--2020-12-31 00:00:00.000


7、去年同一天

SELECT DATEADD(YEAR,-1,GETDATE())
--2019-01-13 20:19:05.987



以上就是今天要给大家介绍的高级日期函数，觉得不错，记得转发分享给更多人，谢谢啦~



——End——


后台回复关键字：资料领取，获取一份精心整理的技术干货

后台回复关键字：进群，带你进入高手如云的交流群。

推荐阅读


点击「阅读原文」了解SQL训练营
最后修改时间：2020-01-14 10:50:38
文章转载自SQL数据库开发，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论