关于SqlServer中datediff用法_Sql Server

关于SqlServer中datediff用法

2022-12-01 16:59muhai Sql Server

datediff是SQL SERVER里面的用法，ORACLE没有，主要作用是返回两个日期之间的时间间隔，本文通过实例代码给大家详细讲解，对datediff用法感兴趣的朋友跟随小编一起看看吧

datediff是SQL SERVER里面的用法，ORACLE没有

一、作用：返回两个日期之间的时间间隔

二、语法：datediff(datepart,startdate,enddate)

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

1、datepart，规定应在日期的哪一部分计算差额

日期缩写

year yy/yyyy

quarter qq/q

Month mm/m

day dd/d

Hour hh/h

minute mi,n

second ss,s

三、测试

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

1、参数是 yyyy的话，统计相差的年份，截取前4位进行统计

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

关于SqlServer中datediff用法

2、参数是 qq时，统计相差的季度

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

关于SqlServer中datediff用法

3、参数是 mm的时候，统计相差的月份，截取前6位进行统计

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

关于SqlServer中datediff用法

4、参数是 dd的时候，统计相差的天数

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

关于SqlServer中datediff用法

5、参数是 hh的时候，统计相差的小时

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

关于SqlServer中datediff用法

6、参数是 mi的时候，统计相差的分钟数

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

关于SqlServer中datediff用法

7、参数是 ss的时候，统计相差的秒数

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

关于SqlServer中datediff用法