SQL日期函数详解与实战

编写SQL中经常可以遇到日期的格式化与日期相关函数,这篇文章中我们将整理一下相关函数并给出一些实例供参考

获取时间函数

getdate()函数

getdate()函数获取当前的日期与时间。返回的为datetime类型

select GETDATE()

返回2020-09-21 10:07:38.170

SYSDATETIME()函数

SYSDATETIME()函数获取当前的日期与时间,但是返回的数据类型是DATETIME2,会更加精准,使用频率较少

SELECT SYSDATETIME()

格式化函数

CONVERT()函数

CONVERT() 函数是一个数据类型转化的通用函数,例如将数字类型转化为文本等。我们可以使用此函数把日期转换为新数据类型
语法为:

        CONVERT(data_type(length),expression,style)

其中data_type(length) 规定目标数据类型(带有可选的长度)。expression指需要转换的值。style 规定日期/时间的输出格式。

其中常用的style代码及显示格式如下:

        101: mm/dd/yyyy
        110: mm-dd-yyyy
        111: yyyy/mm/dd
        112: yyyymmdd
        120: yyyy-mm-dd hh:mm:ss
        121: yyyy-mm-dd hh:mm:sssssss

全部的style代码可以参考:https://www.cnblogs.com/rainman/p/6558261.html

例如;

    SELECT CONVERT(varchar(100), GETDATE(), 111)

通过查看转化后的数据类型,可以看到新的数据类型已变成varchar(100)类型。

--查看数据类型
DECLARE @query nvarchar(max) = 'SELECT CONVERT(varchar(100), GETDATE(), 111) AS [Column1]';
EXEC sp_describe_first_result_set @query, null, 0;

FORMAT()函数

Format函数用于指定显示的格式。
语法:

FORMAT(value,format[,culture])

参数format用于指定显示的格式,给予用户对格式更自由地控制,culture参数是可选的,用于指定显示的语言,该函数返回值的数据类型是NVARCHAR,如果格式转换失败,该函数返回NULL

参数format使用#表示一个数值,参数 format 使用以下占位符来表示日期/时间的格式:

1. yyyy、MM、dd:表示年、月、日
2. hh:mm:ss fffffff:表示时、分、秒、毫秒
3. 使用“/”,“-”等作为连接各个部分(part)的分割符号

例如要将当前日期转化为20200921,则SQL语句为:

SELECT Format(GETDATE(), 'yyyyMMdd')

同时format()函数还可以转变数值类型,使用特定的格式展示等

select FORMAT(123456789,'###-##-####') AS 'Custom Number Result'

对应返回结果为123-45-6789

日期的拆分与拼接(构造)

YEAR(date),MONTH(date),DAY(date)

YEAR(date),MONTH(date),DAY(date)等函数返回给定date的年,月,日
实例:

select year('2020-9-21'),month('2020-9-21'),day('2020-9-21')

DATEPART() 函数

DATEPART() 函数用于返回日期/时间的单独部分,比如年、月、日、小时、分钟等等

语法:

DATEPART(datepart,date)

其中date 参数是合法的日期表达式。datepart为需要取出的数据缩写代码。常用的datepart参数可以是下列的值:

    quarter:季度,取值范围是 1、2、3、4
    week:周在年中的序数,取值范围是 1 - 53
    dayofyear:天在年中的序数,取值范围是 1 - 366
    weekday:天在一周中的序数,取值范围是 1 - 7

全部参数可参阅:https://www.w3school.com.cn/sql/func_datepart.asp

具体实例代码:

--返回年,月份,季度,当前天数统计
SELECT DATEPART(yyyy,GETDATE()) AS Year,
DATEPART(m,GETDATE()) AS Month,
DATEPART(quarter,GETDATE()) AS Quarter,
DATEPART(dayofyear,GETDATE()) AS daynumber

DATENAME()函数,

与DATEPART不同的地方在于返回字符类型,比如Jan,一月等
语法:

DATENAME(datepart,date)

实例:

SELECT DATENAME(month, getdate()) AS 'Month'

对于中英文切换,使用如下语句:

set LANGUAGE 'Simplified Chinese'
select  DATENAME(WEEKDAY,getutcdate())

set LANGUAGE 'us_english'
select  DATENAME(WEEKDAY,getutcdate())

日期计算函数

EOMonth()函数

EOMonth()函数可以返回月份的最后一天。因此我们可以利用这个函数求某个月的最后一天与第一天。
语法:

EOMONTH(start_date [,month_to_add])

其中start_date: 有两种输入方式,能够转换为Date的字符串类型 和 date 数据类型
month_to_add: 是int 类型,能够为正整数,负整数和0,默认值是0,如果省略,那么使用默认值0,表示月份的偏移量。

--查看当前月的最后一天、下一个月的最后一天、上一个月的最后一天
declare @date date
set @date=getdate()

select EOMONTH(@date) as CurrentMonth_EndDay,
    EOMONTH(@date,1) as NextMonth_EndDay,
    EOMONTH(@date,-1) as LastMonth_EndDay

dateadd()函数

DATEADD() 函数在日期中添加或减去指定的时间间隔。
语法:

DATEADD(datepart,number,date)

其中datepart是單位(年,月,天等),number是指定的數值,date是原始日期
如下例的結果是当前时间的两个月后的日期,类型为datetime类型

    SELECT DATEADD(MONTH,2,getdate())

根据上述两个函数我们还可以通过上个月的最后一天+1来获取每个月第一天

select dateadd(day,1,EOMONTH(getdate(),-1)) as CurrentMonth_startDay

同时也可以通过前面所说的日期拼接函数与format函数实现

declare @date date
set @date=getdate()

select DATEFROMPARTS(year(@date),month(@date),1) 

--or

select FORMAT(GETDATE(),'yyyy-MM-01')

DATEDIFF()函数

DATEDIFF()函数计算两个日期之间的间隔,传回带正负符号的整数

语法:

DATEDIFF(datepart,startdate,enddate)

datepart为间隔的单位,可以选择天,周,月等。startdate跟enddate为起始时间

实例:

SELECT DATEDIFF(DAY, '2010-10-03','2010-10-04'  )

结果返回1

构造日期的函数

构造日期的函数有以下几个:
语法:

    DATEFROMPARTS ( year, month, day )
    DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision ) 
    DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
    TIMEFROMPARTS ( hour, minute, seconds, fractions, precision ) 

传入对应参数后即可
其中参数precision 是指小数秒的精度,指的是DateTime2(n)、DateTimeOffset(n),Time(n)中的n值,表示以多少位小数表示1s,一般比较少使用

select DATEFROMPARTS ( 2020, 1, 2 )

练习实例

现有一列日期,请根据日期输出对应的每周开始与每周结束日期并格式化。
例如原始数据time_test表:

| date      | 
|-----------|
| 2020-8-4  | 
| 2020-8-5  | 
| 2020-8-6  | 
| 2020-8-12 | 
| 2020-8-8  | 
| 2020-8-12 | 
| 2020-9-15 |

期望的输出结果:

| date      | period  |
|-----------|---------|
| 2020-8-4  |0803-0809|
| 2020-8-5  |0803-0809|
| 2020-8-6  |0803-0809|
| 2020-8-12 |0810-0816|
| 2020-8-8  | 0803-0809|
| 2020-8-12 | 0803-0809|
| 2020-9-15 |0810-0816|

其中period的开始为每周周一,结束日期为每周周日

解答:
先给出代码

select [date],
format(DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd,-1,[date])), 0),'MMdd')+'-'+format(DATEADD(dd,-1,DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd,6,[date])), 0)),'MMdd') as period
from time_test

从内到外解析这一段代码,我们首先利用datediif(wk,0,date)计算从1900-01-01 到指定日期的周数。之后利用DateAdd函数在1900-01-01 的基础上加上刚刚我们计算出的周数,从而得到每周的开始时间。最后通过forma函数对日期格式化即可得出结果。

其中在最内层有一个DATEADD(dd,-1,[date]),这个是由于我使用的是英文版的SQL server,所以每周是周日开始与周六结束,如果使用中文版SQL server,应该就可以直接使[date]列

我的WX公众号:Romi的杂货铺,欢迎关注!