SQLServer 日期函数用法

SQL Server Date 函数

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

函数	描述
GETDATE()	返回当前日期和时间
DATEPART()	返回日期/时间的单独部分
DATEADD()	在日期中添加或减去指定的时间间隔
DATEDIFF()	返回两个日期之间的时间
CONVERT()	用不同的格式显示日期/时间

日期加减

select Dateadd(day,-10,GETDATE()) as '10天前'

select Dateadd(day,10,GETDATE()) as '10天后'

月加减

select Dateadd(month,-1,GETDATE()) as '1个月前'

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

年加减

select Dateadd(year,-1,GETDATE()) as '1年前'

 

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

截取日期部分

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

 

 

当日期不用精确到时分秒的时候 使用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;

 

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

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 上月总天数

查询结果

 

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

 

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

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 本年第一天

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 本季度最后一天

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

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

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

例如

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

 

如下图所示:

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

而 不是 2019-05-13

 

正确的用法应该使用

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 本周最后一天

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

--周日作为一周第一天(往前推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

--使用循环生成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

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 标准周

参考文档<DATEPART (Transact-SQL) - SQL Server | Microsoft Docs>