SQL Server How to determine if a date range contains a Saturday

9rygscc1 于 2023-05-16 发布在其他

关注(0)|答案(1)|浏览(82)

In my dataset, I have a start datetime field and an end datetime field.

I need to determine if there is a Saturday in this date range (would only need to check for one occurrence as this date range is max 3 days).

For example the below date range - there is one Saturday (the 18th) in this range. I would like to set some sort of flag to indicate that there is a Saturday in the range.

begin_date - 2023-03-17 08:04:02.000, end_date - 2023-03-20 23:17:30.000

Thank you

sql-server

来源：https://stackoverflow.com/questions/76235193/how-to-determine-if-a-date-range-contains-a-saturday

1条答案

按热度按时间

envsm3lx1#

You can do a function like this:

CREATE OR ALTER FUNCTION dbo.FN_HAS_SATURDAY (@pFrom DATE, @pTo DATE)
RETURNS BIT
AS
BEGIN
 
    RETURN (
        CASE 
            WHEN    DATEDIFF(DAY, @pFrom, @pTo) >= 6    -- 6 days or more must pass on saturday
            OR  (DATEPART(DW, @pFrom) + @@DATEFIRST + 5) % 7 + 1 = 6 -- start is saturday
            OR  (DATEPART(DW, @pTo) + @@DATEFIRST + 5) % 7 + 1 = 6 -- end is saturday
            OR (
                (DATEPART(DW, @pFrom) + @@DATEFIRST + 5) % 7 + 1 -- overlapping stuff
            >   (DATEPART(DW, @pTo) + @@DATEFIRST + 5) % 7 + 1 
            AND (DATEPART(DW, @pFrom) + @@DATEFIRST + 5) % 7 + 1 < 6
            )
        THEN 1 ELSE 0 END
    )
    

END

Go
 DECLARE @assert TABLE (dt datetime, dt_to datetime, has_sat bit)
 INSERT INTO @assert
 VALUES ('2023-03-17 08:04:02.000', '2023-03-20 23:17:30.000', 1)
 ,  ('2023-05-12', '2023-05-12 23:17:30.000', 0)
 ,  ('2023-05-14', '2023-05-16 23:17:30.000', 0)
 ,  ('2023-05-08', '2023-05-12', 0)
 ,  ('2023-05-11', '2023-05-13', 1)
 ,  ('2023-05-13 13:00', '2023-05-13', 1)
 ,  ('2023-05-13 13:00', '2023-05-19 23:17:30.000', 1)
 ,  ('2023-05-14 13:00', '2023-05-19 23:17:30.000', 0)
 ,  ('2023-05-14 13:00', '2023-05-20 23:17:30.000', 1)
 ,  ('2023-05-14 13:00', '2023-05-21 23:17:30.000', 1)

SELECT  *, dbo.FN_HAS_SATURDAY(dt, dt_to)
FROM    @assert
WHERE   has_sat <> dbo.FN_HAS_SATURDAY(dt, dt_to)

I got it down to 4 conditions to cover the various cases, maybe someone can optimize a couple of them away, but dating are hard.

You can also convert it to inline function to get better performance or just reuse the case when. Note that i cast the dates to DATE, since times are irrelevant here

赞(0）回复(0）举报 2023-05-16

我来回答

SQL Server How to determine if a date range contains a Saturday

1条答案

相关问题

热门标签

最新问答