SQL中datediff函数如何用?(代码详解)
发布时间:08/01 来源:未知 浏览:
关键词:
+----------+ | Result | |----------| | 365 | +----------+
示例2
下面是另一个例子,我声明了两个变量并为它们分配了两个不一样的日期(我运用DATEADD()将首先个日期增加1年)。然后运用DATEDIFF()返回该日期的各个dateparts:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000'; DECLARE @date2 datetime2 = DATEADD(year, 1, @date1); SELECT DATEDIFF( year, @date1, @date2 ) AS Years, DATEDIFF( quarter, @date1, @date2 ) AS Quarters, DATEDIFF( month, @date1, @date2 ) AS Months, DATEDIFF( week, @date1, @date2 ) AS Weeks, DATEDIFF( dayofyear, @date1, @date2 ) AS DayOfYear, DATEDIFF( day, @date1, @date2 ) AS Days;
效果:
+---------+------------+----------+---------+-------------+--------+ | Years | Quarters | Months | Weeks | DayOfYear | Days | |---------+------------+----------+---------+-------------+--------| | 1 | 4 | 12 | 53 | 366 | 366 | +---------+------------+----------+---------+-------------+--------+
示例3
如前所述,你还可以返回日期之间的工夫局部。下面是返回日期/工夫值之间的小时、分钟和秒数的例子:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000'; DECLARE @date2 datetime2 = DATEADD(hour, 1, @date1); SELECT DATEDIFF( hour, @date1, @date2 ) AS Hours, DATEDIFF( minute, @date1, @date2 ) AS Minutes, DATEDIFF( second, @date1, @date2 ) AS Seconds;
效果:
+---------+-----------+-----------+ | Hours | Minutes | Seconds | |---------+-----------+-----------| | 1 | 60 | 3600 | +---------+-----------+-----------+
示例4
下面是一个猎取两个日期/工夫值之间的毫秒、微秒和纳秒数的例子:
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000'; DECLARE @date2 datetime2 = DATEADD(millisecond, 1, @date1); SELECT DATEDIFF( millisecond, @date1, @date2 ) AS Milliseconds, DATEDIFF( microsecond, @date1, @date2 ) AS Microseconds, DATEDIFF( nanosecond, @date1, @date2 ) AS Nanoseconds;
效果:
+----------------+----------------+---------------+ | Milliseconds | Microseconds | Nanoseconds | |----------------+----------------+---------------| | 1 | 1000 | 1000000 | +----------------+----------------+---------------+
示例5 - Error差错!
要是你尝试做一些极度的事情,比方,返回100年后的纳秒数,你会得到一个差错。这是由于DATEDIFF()返回一个int值,100年内的纳秒数比int数据类型能够处置的纳秒数还要多。
要是你试着这么做会产生什么呢?
DECLARE @date1 datetime2 = '2000-01-01 00:00:00.0000000'; DECLARE @date2 datetime2 = DATEADD(year, 100, @date1); SELECT DATEDIFF( millisecond, @date1, @date2 ) AS Milliseconds, DATEDIFF( microsecond, @date1, @date2 ) AS Microseconds, DATEDIFF( nanosecond, @date1, @date2 ) AS Nanoseconds;
效果:
The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.
固然要是你真的必需寻出100年内有多少纳秒,那么可以运用DATEDIFF_BIG()函数。这个函数返回一个带符号的bigint数据类型,它允许你返回比DATEDIFF()更大的值。