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
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