sql server--使用DATEDIFF函数计算两个日期的间隔分钟

1、DATEDIFF 计算日期差

语法：DATEDIFF(datepart,startdate,enddate)

查询当前时间：SELECT GETDATE()

查询指定日期与当前日期的分钟差：SELECT DATEDIFF(MINUTE, '2019-10-08 16:10:00', GETDATE()) 

秒差：SELECT DATEDIFF(ss, '2019-10-08 16:23:00', GETDATE()) 

小时差：SELECT DATEDIFF(hh, '2019-10-08 14:23:00', GETDATE()) 

天差：SELECT DATEDIFF(dd, '2019-10-02 16:23:00', GETDATE()) 

年差：SELECT DATEDIFF(yy, '2009-10-08 14:23:00', GETDATE()) 

 

2.DATEPART 根据周、月、年统计

语法：DATEPART(datepart,date)

周
SELECT DATEPART(yy,start_time)as 年,DATEPART(mm,start_time)as 月,DATEPART(ww,start_time)as 周,COUNT(*)as 总数 
from IR WHERE start_time BETWEEN '2018-01-01' and '2019-12-12' 
GROUP BY DATEPART(mm,start_time),DATEPART(yy,start_time),DATEPART(ww,start_time) ORDER BY DATEPART(yy,start_time)

月

SELECT DATEPART(yy,start_time)as 年,DATEPART(mm,start_time)as 月,COUNT(*)as 总数 
from IR WHERE start_time BETWEEN '2018-01-01' and '2019-12-12' 
GROUP BY DATEPART(mm,start_time),DATEPART(yy,start_time)

年
SELECT DATEPART(yy,start_time)as 年,COUNT(*)as 总数 
from IR WHERE start_time BETWEEN '2018-01-01' and '2019-12-12' 
GROUP BY DATEPART(yy,start_time)

缩写参考：