SQL Server 日期函数用法

Red_Q.

SQL Server 日期函数的各种用法

下面的表格列出了 SQL Server 中最重要的内建日期函数：

函数

描述

GETDATE()

返回当前日期和时间

DATEPART()

返回日期/时间的单独部分

DATEADD()

在日期中添加或减去指定的时间间隔

DATEDIFF()

返回两个日期之间的时间

CONVERT()

用不同的格式显示日期/时间

日期加减

select Dateadd(day,–10,GETDATE()) as ’10天前‘

2.png

select Dateadd(day,10,GETDATE()) as ’10天后‘

1644463974716256.png

月加减

select Dateadd(month,–1,GETDATE()) as ‘1个月前‘

3.png

select Dateadd(month,1,GETDATE()) as ‘1个月后‘

4.png

年加减

select Dateadd(year,–1,GETDATE()) as ‘1年前‘

5.png

select Dateadd(year,1,GETDATE()) as ‘1年后‘

6.png

截取日期部分

select convert(date,getdate()–1,23)  昨天

7.png

8.png

当日期不用精确到时分秒的时候 使用Dateadd(day,-1,Getdate()) 函数 会造成数据 差异 , 例如 2019-06-10 00:00:00 并不小于等于 Dateadd(day,-1,Getdate())

计算月时间进度

select

day(getdate()) as 本月第几天,

day(dateadd(month,datediff(month,–1,getdate()),–1)) as 本月总天数,

cast(convert(decimal(18,2),day(getdate()))/convert(decimal(18,2),day(dateadd(month,datediff(month,–1,getdate()),–1))) as decimal(18,4)) as 时间进度

查询今天是今年的第几天

select Datepart(dy,getdate()) as 今天是今年的第几天

select Datediff(day,Dateadd(yy, Datediff(yy,0,Getdate()), 0),Dateadd(yy, Datediff(yy,0,getdate())+1, 0)) as 今年天数

select Dateadd(yy, Datediff(yy,0,Getdate()), 0) as 今年第一天

select Dateadd(day,–1,Dateadd(yy, Datediff(yy,–1,getdate()), 0)) as 今年最后一天

SQL Server 生成指定日期范围内的每一天

DECLARE @S_DATE varchar(10),@E_DATE varchar(10);

SET @S_DATE=‘2019-05-01’;

SET @E_DATE=‘2019-05-10’;

SELECT

CONVERT (VARCHAR(10),dateadd(day,number,@S_DATE),23) AS every_day

FROM master..spt_values n

WHERE n.type = ‘p’ AND n.number<=datediff(day,@S_DATE,@E_DATE)

order by every_day;

9.png

生成指定日期当月的每一天,截止到今天

SELECT

CONVERT (VARCHAR(10),dateadd(day,number,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)),23) AS every_day

FROM master..spt_values n

WHERE n.type = ‘p’ AND n.number<=datediff(day,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0),getdate())

order by every_day;

生成指定日期一整个月的每天

SELECT

CONVERT (VARCHAR(10),dateadd(day,number,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)),23) AS every_day

FROM master..spt_values n

WHERE n.type = ‘p’ AND n.number<=datediff(day,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0),Dateadd(month,Datediff(month,–1,Getdate()), –1))

order by every_day;

生成指定日期当年的每一天

SELECT

CONVERT (VARCHAR(10),dateadd(day,number,Dateadd(yy, Datediff(yy,0,Getdate()), 0)),23) AS every_day

FROM master..spt_values n

WHERE n.type = ‘p’ AND n.number<=datediff(day,Dateadd(yy, Datediff(yy,0,Getdate()), 0),getdate())

order by every_day;

—生成今年第一天到今天日期

SELECT

CONVERT (VARCHAR(10),dateadd(day,number,Dateadd(yy, Datediff(yy,0,Getdate()), 0)),23) AS every_day into #TMP_EVERYDAY

FROM master..spt_values n

WHERE n.type = ‘p’ AND n.number<=datediff(day,Dateadd(yy, Datediff(yy,0,Getdate()), 0),getdate()–1)

order by every_day;

select

Dateadd(dd, Datediff(dd,0,Getdate()), 0) as 今天,

Dateadd(mm, Datediff(mm,0,Getdate()), 0) as 本月第一天,

Dateadd(month,Datediff(month,–1,Getdate()), –1) as 本月最后一天,

Dateadd(mm, Datediff(mm,0,Getdate())–1, 0) as 上月第一天,

Dateadd(month,datediff(month,–1,Getdate())–1, –1) as 上月最后一天,

Dateadd(yy, Datediff(yy,0,Getdate()), 0) as 今年第一天,

Dateadd(yy, Datediff(yy,0,getdate())+1, –1) as 今年最后一天,

Dateadd(yy, Datediff(yy,0,Getdate())–1, 0) as 去年第一天,

Dateadd(yy, Datediff(yy,0,getdate()), –1) as 去年最后一天,

Day(Dateadd(month, Datediff(month,–1,Getdate()), –1)) as 本月总天数,

Day(Dateadd(month, Datediff(month,–1,Getdate())–1, –1)) as 上月总天数

查询结果

10.png

1.一个月第一天的

SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)

2.本周的星期一

SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

3.一年的第一天

SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)

4.季度的第一天

SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

5.当天的开始

SELECT DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)

计算原理为

在SQL Server 中

Select Dateadd(day,0,0) as 起始日期  返回的是 1900-01-01 00:00:00 000

11.png

Select Datediff(Year,0,Getdate()) as ‘1900年到今天过去的年数‘  则返回 从1900 年~今天过去的年数

12.png

Select Dateadd(Year,Datediff(Year,0,Getdate()),0)

获取的 就是 1900-01-01 00:00:00 000  年份 +119

select DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,getdate())),0) as 本周第一天

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) as 本月第一天

select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as 本季第一天

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) as 本年第一天

13.png

select

DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,getdate())),0) as 本周第一天,

DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,getdate())),6) as 本周最后一天,

Dateadd(mm, Datediff(mm,0,Getdate()), 0) as 本月第一天,

Dateadd(month,Datediff(month,–1,Getdate()), –1) as 本月最后一天,

DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) as 本季度第一天,

Dateadd(dd,–1,DATEADD(qq, DATEDIFF(qq,0,getdate())+1, 0)) as 本季度最后一天

14.png

注意: 获取本周第一天的时候不能使用

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) as 本周第一天

这会导致 在周日的时候把本周第一天算到明天去

例如

select DATEADD(wk, DATEDIFF(wk,0,‘2019-05–12‘), 0) as 本周第一天

15.png

如下图所示:

2019-05-12 的本周第一天应该是2019-05-06

而 不是 2019-05-13

16.png

正确的用法应该使用

select

DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,‘2019-05-12’)),0) as 本周第一天,

DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,‘2019-05-12’)),6) as 本周最后一天

或者

select 

DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,‘2019-05-12’)),0) as 本周第一天,

DATEADD(dd,6,DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,‘2019-05-12’)),0)) as 本周最后一天

17.png

如果 一周的第一天要从周日算起则使用(错误写法,请勿使用,该写法今天为周一时会出错)

—周日作为一周第一天(往前推1天)

select

Dateadd(dd,–1,DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,getdate())),0)) as 本周第一天,

Dateadd(dd,–1,DATEADD(wk, DATEDIFF(wk,0,DATEADD(dd, –1,getdate())),6)) as 本周最后一天

SQL Server 生成指定日期之前的每个月最后一天

SELECT

Dateadd(month,datediff(month,–1,getdate())–(number+1), –1) as lastday_of_month

FROM master..spt_values n

WHERE n.type = ‘p’ and n.number<=11 and n.number<Month(getdate())–1

order by lastday_of_month

18.png

—使用循环生成7天前每天日期

Declare

@S_DATE date

set @S_DATE=convert(varchar(10),getdate()–7,23)

while @S_DATE<convert(varchar(10),getdate(),23)

begin

    print @S_DATE

    set @S_DATE=DATEADD(day,1,@S_DATE)

end

19.png

SQL Server 判断今天是星期几

select

case datepart(dw,getdate())

when 1 then ‘星期天 ‘

when 2 then ‘星期一 ‘

when 3 then ‘星期二 ‘

when 4 then ‘星期三 ‘

when 5 then ‘星期四 ‘

when 6 then ‘星期五 ‘

when 7 then ‘星期六 ‘

end

—取今年本周与去年本周

—因为今年本周周一,与去年本周周一,不一定是同一天

—为确保天数是一样的应使用以下方法

先利用

Datediff(day,DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,–1,@RQ)),0),@RQ)

求出今年本周过去的天数,

再用Dateadd( ) 往前推 Datediff(day,DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,–1,@RQ)),0),@RQ)求出来的天数

declare @RQ datetime

set @RQ=‘2020-03-01’

select DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,–1,@RQ)),0),@RQ

select DATEADD(dd,–(Datediff(day,DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,–1,@RQ)),0),@RQ)),Dateadd(mm,–12,@RQ)),Dateadd(mm,–12,@RQ)

–日期格式

datepart

缩写形式

含义

year

yy, yyyy

年

quarter

qq, q

季度

month

mm, m

月

dayofyear

dy, y

一年中的第几天

day

dd, d

日

week

wk, ww

周

weekday

dw

周几

hour

hh

时

minute

mi, n

分

second

ss, s

秒

millisecond

ms

毫秒

microsecond

mcs

微秒

nanosecond

ns

纳秒

TZoffset

tz

时间区

ISO_WEEK

isowk, isoww

ISO 标准周

来自 <https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datepart-transact-sql?view=sql-server-2016>

SQL Server 日期函数用法

转载请注明来源：幽幽过客_{SQL Server 日期函数用法}