DATEDIFF函数,用来查询两个时间值之间的数据

Sql Server中的DATEDIFF函数功能:可以直接支持两个年份、两个月份、两个周、天等两个时间之间数据值查询,它提供了三个参数,写法:DATEDIFF ( datepart , startdate , enddate )  ,“datepart” DATEDIFF 用于报告 startdate 与 enddate 之间差异的单位。 常用 datepart 单位包括 month 或 second。“startdate”开始时间设定,可解析为下列值之一的表达式:date、datetime、 datetimeoffset、datetime2、smalldatetime、time。“ enddate”结束时间设定取值类型和“startdate”相同。微软官方文档示例:https://docs.microsoft.com/zh-cn/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15

SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

ATEDIFF 接受 datepart 的全名或任何列出的全名缩写形式:
datepart 名称 datepart namedatepart 缩写 datepart abbreviation
year(年)yy, yyyyyy, yyyy
quarter(季度)qq, qqq, q
month(月)mm, mmm, m
dayofyear(年中日)dy, ydy, y
day(日)dd, ddd, d
week(周)wk, wwwk, ww
hour(小时)hhhh
minute(分钟)mi, nmi, n
second(秒)ss, sss, s
millisecond(毫秒)ms ms
microsecond(微秒)mcs mcs
nanosecond(纳秒)ns ns

----------------------------------------------------------------------------------------------------------------------------

MySql中的DATEDIFF函数功能:DATEDIFF(enddate,startdate)这里开始时间设定在第二个参数,结束时间设定在第一个参数,用法:“ SELECT DATEDIFF('2019-08-18','2019-08-08'); --10 days”。MySql官方介绍:https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_datediff