函数： DATEDIFF(datepart,startdate,enddate)



具体实例：

1  --相差年数 结果0
2  SELECT DATEDIFF(yy,'2008-12-29','2008-12-1') AS DiffDate
3  
4  --相差年数 结果1
5  SELECT DATEDIFF(yy,'2008-12-29','2009-12-01') AS DiffDate

1  --相差季度 结果1
2  SELECT DATEDIFF(qq,'2008-02-02','2008-06-30') AS DiffDate
3  
4  --相差季度 结果2
5  SELECT DATEDIFF(qq,'2008-02-01','2008-07-01') AS DiffDate

 

1  
2  --相差月 结果4
3  SELECT DATEDIFF(m,'2008-02-02','2008-06-30') AS DiffDate
4  
5  --相差月 结果5
6  SELECT DATEDIFF(m,'2008-02-10','2008-07-06') AS DiffDate

1  --相差天数 结果120
2  SELECT DATEDIFF(dy,'2008-03-02','2008-06-30') AS DiffDate
3  
4  --相差天数 结果0
5  SELECT DATEDIFF(dy,'2008-02-10','2008-02-10') AS DiffDate

注意：我这里设置了datepart = dy 或者 dd 感觉没啥区别，路过的小伙伴求解答疑惑？

 

 

1  --相差小时 结果 20h
2  SELECT DATEDIFF(hh,'2008-03-02 01:01:01','2008-03-02 21:01:01') AS DiffDate
3   
4  --相差小时 结果 20h
5 SELECT DATEDIFF(hh,'2008-03-02 01:01:01','2008-03-02 21:59:01') AS DiffDate
6 
7  --相差小时 结果 1h
8 SELECT DATEDIFF(hh,'2008-03-02 01:10:01','2008-03-02 02:11:02') AS DiffDate

 

1  --相差秒数 结果 -10s
2  SELECT DATEDIFF(s,'2008-03-02 10:10:30','2008-03-02 10:10:20') AS DiffDate
3  
4  --相差秒数 结果28800s
5  SELECT DATEDIFF(s,'2019-06-04 01:01:01','2019-06-04 09:01:01') AS DiffDate

 

参考资料：http://www.w3school.com.cn/sql/func_datediff.asp