sql-server - 检查时间是否在 SQL Server 中的两个日期之间

Question

我很难获得两个日期之间的确切总休息时间。这是我的查询

SELECT
    FORMAT(CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2), 'MM/dd/yyyy  hh:mm:ss tt') as Start_Time,
    FORMAT(CAST(CONCAT(a.end_date,' ',a.end_time) as datetime2), 'MM/dd/yyyy hh:mm:ss tt') as End_Time,
    DATEDIFF(MINUTE, CONCAT(a.start_date,' ',a.start_time),   CONCAT(a.end_date,'',a.end_time)) / 60.0 as TotalTime,
    c.break_from,
    c.break_to,
    ISNULL((DATEDIFF(MINUTE, c.break_from,c.break_to) / 60.0 *DATEDIFF(DAY,   a.start_date, a.end_date)+1), 0)as TotalBreak,
    ISNULL((DATEDIFF(MINUTE, CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2), CAST(CONCAT(a.end_date,' ',a.end_time) as datetime2))/60.0), 0) as  OriginalTime,
    ISNULL(((DATEDIFF(MINUTE, break_start, break_end))*(DATEDIFF(DAY, a.start_date, a.end_date)+1)), 0) as Break_,
    ISNULL((((DATEDIFF(MINUTE, CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2), CAST(CONCAT(a.end_date,' ',a.end_time) as datetime2))) - 
    ISNULL((((DATEDIFF(minute, break_start, break_end))*(DATEDIFF(DAY, a.start_date, a.end_date)+1)), 0))/60.0), 0)as EstimatedTotalWorkHours,
    ISNULL(c.code, 'TIME14') as TimeReference
FROM 
    spms_tblSubTask AS a
LEFT JOIN 
    pmis.dbo.employee as b ON a.eid = b.eid
LEFT JOIN 
    pmis.dbo.time_reference as c ON c.code = ISNULL(b.TimeReference, 'TIME14')
CROSS APPLY
    (SELECT 
         break_start = CASE WHEN CAST(CONCAT(a.start_date,' ',c.break_from) as datetime2) between CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2)and CAST(CONCAT(a.end_date,' ',a.end_time) as datetime2)
                    then CAST(CONCAT(a.start_date,' ',c.break_from) as datetime2)
                    when CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2) between CAST(CONCAT(a.start_date,' ',c.break_from) as datetime2) and CAST(CONCAT(a.start_date,' ',c.break_to) as datetime2)
                    then CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2)
                    else NULL
                    end,
                break_end  = case   when CAST(CONCAT(a.start_date,' ',c.break_to) as datetime2) between CAST(CONCAT(a.start_date,' ',a.start_time) as datetime2) and CAST(CONCAT(a.end_date,' ',a.end_time) as datetime2)
                    then CAST(CONCAT(a.start_date,' ',c.break_to) as datetime2)
                    when CAST(CONCAT(a.start_date,' ',a.end_time) as datetime2) between CAST(CONCAT(a.start_date,' ',c.break_from) as datetime2) and CAST(CONCAT(a.start_date,' ',c.break_to) as datetime2)
                    then CAST(CONCAT(a.start_date,' ',a.end_time) as datetime2)
                    end) as d
WHERE
    b.Shift = 0

输出：

在这里我们可以看到ROW 1它完全按照我想要的方式工作，但请注意，ROW 4它9:00 am在第二天结束意味着它不会在第三天的休息时间落下。我该如何解决这个问题？谢谢

标签： sql-serverdatetime

Answer 1

I don't understand completely what you want, but this might be helpful:

declare @time time = '17:00'    

Select 
    a.DateStarted,
    convert( datetime, convert( date, a.DateStarted ) ) + convert( datetime, @time ),
    a.DateFinished
From
    SomeTable as a    
Where
    (
        ( a.DateStarted <= convert( datetime, convert( date, a.DateStarted ) ) + convert( datetime, @time ) )
        and ( convert( datetime, convert( date, a.DateStarted ) ) + convert( datetime, @time ) < a.DateFinished)  
    )