SQL Server中计算日期差的方法-猿码集

SQL Server中计算日期差的方法

在SQL Server中，我们经常需要计算日期之间的差值。例如，计算两个日期之间的天数、小时数、分钟数等等。本文将介绍如何使用SQL Server中内置的函数来计算日期之间的差值。

01. 使用DATEDIFF函数计算日期差

SQL Server中提供了一个名为DATEDIFF的函数，可以用来计算两个日期之间的差值。DATEDIFF函数的语法如下：


DATEDIFF(interval, startdate, enddate)

其中，interval指定计算的时间单位，可以是year、quarter、month、day、hour、minute、second等，startdate和enddate分别是要计算的两个日期。

例如，我们要计算2021年5月1日和2022年4月30日之间的天数差：


DECLARE @startdate DATETIME = '2021-05-01';
DECLARE @enddate DATETIME = '2022-04-30';
SELECT DATEDIFF(day, @startdate, @enddate) AS day_diff;

输出：

day_diff --------- 364

可以看到，两个日期之间相差了364天，即一年中的天数。

02. 使用DATEDIFF函数计算其他时间单位的差

除了天数差之外，我们还可以使用DATEDIFF函数来计算其他时间单位的差，例如小时差、分钟差、秒差等等。下面是几个例子：

计算小时差：


DECLARE @startdate DATETIME = '2021-05-01 00:00:00';
DECLARE @enddate DATETIME = '2021-05-01 12:00:00';
SELECT DATEDIFF(hour, @startdate, @enddate) AS hour_diff;

输出：

hour_diff --------- 12

计算分钟差：


DECLARE @startdate DATETIME = '2021-05-01 00:00:00';
DECLARE @enddate DATETIME = '2021-05-01 00:30:00';
SELECT DATEDIFF(minute, @startdate, @enddate) AS minute_diff;

输出：

minute_diff ----------- 30

计算秒差：


DECLARE @startdate DATETIME = '2021-05-01 00:00:00';
DECLARE @enddate DATETIME = '2021-05-01 00:00:10';
SELECT DATEDIFF(second, @startdate, @enddate) AS second_diff;

输出：

second_diff ----------- 10

03. 计算日期精确到毫秒的差

如果需要计算日期精确到毫秒的差，可以使用DATEDIFF函数和CAST函数结合起来来实现。例如，计算2021年5月1日00:00:00.123和2021年5月1日00:00:00.456之间的毫秒差：


DECLARE @startdate DATETIME = '2021-05-01 00:00:00.123';
DECLARE @enddate DATETIME = '2021-05-01 00:00:00.456';
SELECT DATEDIFF(millisecond, @startdate, @enddate) AS millisecond_diff;

输出：

millisecond_diff ---------------- 333

04. 计算日期之间的工作日差和工作小时差

有时候，我们需要计算日期之间的工作日差和工作小时差。在SQL Server中，可以使用自定义函数来实现这个功能。下面是一个计算两个日期之间工作日差和工作小时差的UDF函数：


CREATE FUNCTION dbo.fnGetBusinessDaysDiff(@startdate DATETIME, @enddate DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @workdays INT;
    DECLARE @businesshours INT;
    SELECT @workdays = 
    (DATEDIFF(dd,@startdate,@enddate)+1)
    -(DATEDIFF(wk,@startdate,@enddate)*2)
    -(CASE WHEN DATEPART(dw,@startdate)=1 THEN 1 ELSE 0 END)
    +(CASE WHEN DATEPART(dw,@enddate)=1 THEN 1 ELSE 0 END)
    -(CASE WHEN DATEPART(dw,@startdate)=7 THEN 1 ELSE 0 END)
    +(CASE WHEN DATEPART(dw,@enddate)=7 THEN 1 ELSE 0 END)
    ,@businesshours = 
    datediff(hour, 
    dateadd(dd,datediff(dd,0,@startdate), CAST('09:00' AS datetime2)),
    dateadd(dd,datediff(dd,0,@enddate), CAST('17:00' AS datetime2))
    )
    +(datediff(minute,@startdate, dateadd(dd,datediff(dd,0, @startdate), CAST('09:00' AS datetime2)))
    +
    datediff(minute,dateadd(dd,datediff(dd,0,@enddate), CAST('17:00' AS datetime2)),@enddate));
    -- 返回工作日差
    RETURN @workdays;
END;
GO

现在，我们可以使用这个自定义函数来计算日期之间的工作日差和工作小时差。例如，计算2021年5月1日和2021年5月10日之间的工作日差和工作小时差，假设工作时间是每天从9点到17点：


DECLARE @startdate DATETIME = '2021-05-01';
DECLARE @enddate DATETIME = '2021-05-10';
SELECT dbo.fnGetBusinessDaysDiff(@startdate, @enddate) AS business_days_diff,
       dbo.fnGetBusinessDaysDiff(@startdate, @enddate) * 8 AS business_hours_diff;

输出：

business_days_diff business_hours_diff ------------------ ------------------- 6 48

可以看到，两个日期之间的工作日差是6天，工作小时差是48小时。

总结

在SQL Server中，计算日期之间的差值是一个常见的需求。通过使用SQL Server内置的DATEDIFF函数，我们可以很方便地计算日期之间的天数差、小时差、分钟差、秒差等等。如果需要计算日期精确到毫秒的差，可以使用DATEDIFF函数和CAST函数结合起来来实现。如果需要计算日期之间的工作日差和工作小时差，可以使用自定义函数来实现。

SQL Server中计算日期差的方法