關(guān)于SqlServer中datediff用法

更新時(shí)間：2022年11月02日 16:01:16 投稿：mrr

datediff是SQL SERVER里面的用法，ORACLE沒有，主要作用是返回兩個(gè)日期之間的時(shí)間間隔，本文通過實(shí)例代碼給大家詳細(xì)講解，對(duì)datediff用法感興趣的朋友跟隨小編一起看看吧

一、作用：返回兩個(gè)日期之間的時(shí)間間隔

二、語(yǔ)法：datediff(datepart,startdate,enddate)

是后面日期減去前面的日期(enddate-startdate)

1、datepart，規(guī)定應(yīng)在日期的哪一部分計(jì)算差額

日期縮寫

year yy/yyyy

quarter qq/q

Month mm/m

day dd/d

Hour hh/h

minute mi,n

second ss,s

三、測(cè)試

SQL SERVER不用像ORACLE一樣寫 select ... from dual，只需要寫select ....就可以了

1、參數(shù)是 yyyy的話，統(tǒng)計(jì)相差的年份，截取前4位進(jìn)行統(tǒng)計(jì)

select datediff(yyyy,'20210928','20200121')test
--select datediff(year,'20210928','20200121')test
--select datediff(yy,'20210928','20200121')test

2、參數(shù)是 qq時(shí)，統(tǒng)計(jì)相差的季度

select datediff(qq,'20210928','20200121')test
--select datediff(q,'20210928','20200121')test
--select datediff(quarter,'20210928','20200121')test

3、參數(shù)是 mm的時(shí)候，統(tǒng)計(jì)相差的月份，截取前6位進(jìn)行統(tǒng)計(jì)

select datediff(mm,'20210928','20200121')test
--select datediff(m,'20210928','20200121')test
--select datediff(month,'20210928','20200121')test

4、參數(shù)是 dd的時(shí)候，統(tǒng)計(jì)相差的天數(shù)

select datediff(dd,'20210928','20200121')test
--select datediff(day,'20210928','20200121')test
--select datediff(d,'20210928','20200121')test

5、參數(shù)是 hh的時(shí)候，統(tǒng)計(jì)相差的小時(shí)

select datediff(hour,'20211112 10:00:00','20211112 00:00:00')test_hour
       ,datediff(hh,'20211112 10:00:00','20211112 00:00:00')test_hh

6、參數(shù)是 mi的時(shí)候，統(tǒng)計(jì)相差的分鐘數(shù)

select datediff(minute,'20211112 10:00:00','20211112 00:00:00')test_minute
       ,datediff(mi,'20211112 10:00:00','20211112 00:00:00')test_mi
       ,datediff(n,'20211112 10:00:00','20211112 00:00:00')test_n

7、參數(shù)是 ss的時(shí)候，統(tǒng)計(jì)相差的秒數(shù)

select datediff(second,'20211112 10:00:00','20211112 00:00:00')test_second
       ,datediff(ss,'20211112 10:00:00','20211112 00:00:00')test_ss
       ,datediff(s,'20211112 10:00:00','20211112 00:00:00')test_s