T-Sql 2005 将时间添加到日期时间字段，结果在工作时间内

Question

我有两个日期时间字段，我希望将它们添加在一起。 它们的格式如下：“01/01/1900 00:00:00”。

主要问题是我希望计算仅包括工作时间。 工作日在 08:30 到 17:30 之间，不包括周末：

此外，如果第一个字段在工作日之外开始或在周末，则应从下一个工作日开始添加第二个字段。

例如：

“26/06/2009 15:45:00”+“01/01/1900 09:00:00”=“29/06/1900 15:45:00”“

12/07/2009 14: 22:36' + '01/01/1900 18:00:00' = '13/07/1900 08:30:00'

'15/07/2009 08:50:00' + '01/01/1900 04 :00:00' = '15/07/2009 12:50:00'`

我非常确定这将涉及创建一个用户定义的函数来解决这个问题，但我不知道如何开始这个（我是超出了我的深度）任何人都可以为我提供一些关于如何实现这一目标的建议吗？

Answer 1

试试这个，你可能需要把它放在一个函数中

DECLARE @Date DATETIME,
        @StartOfDay FLOAT,
        @EndOfDay FLOAT,
        @DateAdd DATETIME

SELECT  @Date ='2009-06-26 15:45:00.000',
        @StartOfDay = 8.5,
        @EndOfDay = 17.5,
        @DateAdd = '1900-01-01 09:00:00.000'

--fix up start date
--before start of day, move to start of day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) < @StartOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date))
END

--after close of day, move to start of next day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) > @EndOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1
END

--move to monday if on weekend
WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday')
BEGIN
    SET @Date = @Date + 1
END

--get the number of hours to add and the total hours per day
DECLARE @HoursPerDay FLOAT
DECLARE @HoursAdd FLOAT
SET @HoursAdd = DATEDIFF(hh, '1900-01-01 00:00:00.000', @DateAdd)
SET @HoursPerDay = @EndOfDay - @StartOfDay

--date the time of geiven day
DECLARE @CurrentHours FLOAT
SET @CurrentHours = CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24

--if we stay in the same day, all is fine
IF (@CurrentHours + @HoursAdd <= @EndOfDay)
BEGIN
    SET @Date = @Date + @DateAdd
END
ELSE
BEGIN
    --remove part of day
    SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours)
    --,ove to next day
    SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1

    --loop day
    WHILE @HoursAdd > 0
    BEGIN
        --add day but keep hours to add same
        IF (DATENAME(dw,@Date) IN ('Saturday','Sunday'))
        BEGIN
            SET @Date = @Date + 1
        END
        ELSE
        BEGIN
            --add a day, and reduce hours to add
            IF (@HoursAdd > @HoursPerDay)
            BEGIN
                SET @Date = @Date + 1
                SET @HoursAdd = @HoursAdd - @HoursPerDay
            END
            ELSE
            BEGIN
                --add the remainder of the day
                SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date))
                SET @HoursAdd = 0
            END
        END     
    END
END

SELECT @Date

希望有帮助

Answer 2

您可以使用 dayofweek 函数和一些内联 case 语句；

http://www.smallsql.de/doc/sql-函数/date-time/dayofweek.html

http://www.tizag.com /sqlTutorial/sqlcase.php

因此，如果 dayofweek 函数没有返回 sat，您将执行计算。 或太阳。 否则返回 null。

我认为你可以不用编写用户定义的函数就可以摆脱困境，但是sql语句看起来有点混乱。 但话又说回来，大多数非基本 SQL 语句看起来都有点混乱！

Answer 3

/*
This sample down below updating  previous material an shown what's happened if you want to ADD more than 23 hours to a date:
*/


USE [REP]
GO
/****** Object:  UserDefinedFunction [dbo].[fct_add_hours_depend_workshift_L-V_0830_1830]    Script Date: 11/26/2018 3:22:37 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*

UTILIZATION

SELECT
GETDATE () as crt_date
,[rep].[dbo].[fct_add_hours_depend_workshift_L-V_0830_1830]
            (
                GETDATE()
                ,number_of_hours_as_integer -- ex: 96
            ) as_expiration_date

ORIGINAL SOURCE:

https://stackoverflow.com/questions/1130721/t-sql-2005-adding-hours-to-a-datetime-field-with-the-result-within-working-hours

*/


ALTER FUNCTION [dbo].[fct_add_hours_depend_workshift_L-V_0830_1830]
(
@Date DATETIME,
@HrsAdd INT
)

RETURNS DATETIME

as

BEGIN

DECLARE @StartOfDay FLOAT,
        @EndOfDay FLOAT

-- workshift declaration de la 8:30 la 18:30
SELECT  @StartOfDay = 8.5,
        @EndOfDay = 18.5


--fix up start date
--before start of day, move to start of day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) < @StartOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date))
END




--after close of day, move to start of next day
IF ((CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24) > @EndOfDay)
BEGIN
    SET @Date = DATEADD(mi, @StartOfDay * 60, DATEDIFF(dd,0,@Date)) + 1
END

--move to monday if on weekend
WHILE DATENAME(dw, @Date) IN ('Saturday','Sunday')
BEGIN
    SET @Date = @Date + 1
END

--get the number of hours to add and the total hours per day
DECLARE @HoursPerDay FLOAT
DECLARE @HoursAdd FLOAT
DECLARE @DateAdd DATETIME

/*
Pentru ca scriptul initial nu-ti permite sa adaugi mai mult de 23 ore am facut o modificare:
- daca trebuie sa adaug mai putin de 23 de ore merg pe clasicul definit de dezvoltator
- daca trebuie sa adaug mai mult de 23 ore fac impartirea la 23 cu rest : ex 96 impartit cu rest la 23 = 4 zile (de workshift L-V 8:30-18:30) si 4 ore (de workshift L-V 8:30-18:30) si voi folosi numai intregul sau daca vrei 96/23 si iau  modulo
*/

-- Do I have to add more than 23 hours?

IF (@HrsAdd > 23)
BEGIN

SET @DateAdd = DATEADD
                        (dd,
                        @HrsAdd/23,
                    CAST('1900-01-01 00:00:00.000' as DATETIME)
                        )
END

ELSE

SET @DateAdd = CAST('1900-01-01 ' + CAST(@HrsAdd as NVARCHAR(2)) + ':00:00.000' as DATETIME)


SET @HoursAdd = DATEDIFF(hh, '1900-01-01 00:00:00.000', @DateAdd)
SET @HoursPerDay = @EndOfDay - @StartOfDay

--date the time of geiven day
DECLARE @CurrentHours FLOAT
SET @CurrentHours = CAST(@Date - DATEADD(dd,0, DATEDIFF(dd,0,@Date)) AS FLOAT) * 24

--if we stay in the same day, all is fine
IF (@CurrentHours + @HoursAdd <= @EndOfDay)
BEGIN
    SET @Date = @Date + @DateAdd
END
ELSE
BEGIN
    --remove part of day
    SET @HoursAdd = @HoursAdd - (@EndOfDay - @CurrentHours)
    --,ove to next day
    SET @Date = DATEADD(dd,0, DATEDIFF(dd,0,@Date)) + 1

    --loop day
    WHILE @HoursAdd > 0
    BEGIN
        --add day but keep hours to add same
        IF (DATENAME(dw,@Date) IN ('Saturday','Sunday'))
        BEGIN
            SET @Date = @Date + 1
        END
        ELSE
        BEGIN
            --add a day, and reduce hours to add
            IF (@HoursAdd > @HoursPerDay)
            BEGIN
                SET @Date = @Date + 1
                SET @HoursAdd = @HoursAdd - @HoursPerDay
            END
            ELSE
            BEGIN
                --add the remainder of the day
                SET @Date = DATEADD(mi, (@HoursAdd + @StartOfDay) * 60, DATEDIFF(dd,0,@Date))
                SET @HoursAdd = 0
            END
        END     
    END
END
RETURN @Date

END