摘要:
下文主要讲述 DATEDIFF 系统函数的使用说明,
实验环境:
sqlserver 2000
如下所示:
DATEDIFF 语法及功能简介
DATEDIFF 功能说明:
返回两个日期之间的差值,并将差值采用分钟、小时、天、年等形式返回。
———————-
DATEDIFF 语法说明:
DATEDIFF(datepart,startdate,enddate)
参数说明:
datepart:
参数名称 |
参数缩写 |
参数说明 |
year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
|
day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw, w |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
|
microsecond |
mcs |
|
nanosecond |
ns |
|
startdate、enddate:
此处表达式需为一个time、date、smalldatetime、datetime、datetime2 或 datetimeoffset 类型的值,
如果输入的为字符串,那么字符串一定要能转换为日期类型
————————–
DATEDIFF 返回值说明:
int
注意事项:
返回指定的 startdate 和 enddate 之间差,并按照datepart规定的类型转换为int。
当返回值超过int的最大数据范围,则返回相应的错误信息
DATEDIFF 举例说明
--例1:
SELECT DATEDIFF(year, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2018-10-12 23:59:59.9999999', '2017-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2018-10-12 23:59:59.9999999', '2018-10-01 00:00:00.0000000');
--例2:在表中进行相关计算
CREATE TABLE dbo.test
(
a datetime2
,b datetime2
);
INSERT INTO dbo.test(a,b)
VALUES('2018-05-06 10:09:09','2018-05-07 10:09:09');
SELECT DATEDIFF(day,a,b) AS '间隔天数'
FROM dbo.test
go;
drop table test
go