'Filter day time column rules using a date

I have this table, wherein the columns corresponds to the days of the week together with fromTime to toTime. I was wondering if how can I filter the table by a date. I have this code below. The date must return row 3 since it's the first one that satisfies the rule.

My table property is like this:

Monday Tuesday Wednesday Thursday Friday FromTime ToTime
1 0 0 0 0 00:02:00 23:59:59
0 1 1 0 0 00:01:00 23:59:59
0 0 0 1 1 00:00:00 23:59:59

With a sample date like '2022-03-03 00:00:00.0000000', it will satisfy row 3 since it's a Thursday and it's within fromTime and toTime

I was thinking of using the DAY() and DATEPART() function like the sample below but I can't think of a way to do it.

SELECT day ('2022-03-03 00:00:00.0000000') as Day
      , DATEPART (HOUR,'2022-03-03 01:30:00.0000000') as FromTime
      , DATEPART (HOUR,'23:59:59.0000000') as ToTime

Result:

Day FromTime ToTime
3 1 23

*edit: removed image

sqlsql-server


Solution 1:[1]

The query would probably be simpler if the day of week were a single column, storing a number. But if you have to work with the existing structure, try identifying the day of week number, then compare it against each "day" column:

DECLARE @DOW INT 
SET @DOW = ((DatePart(dw, getDate()) + @@DATEFIRST-1) % 7+1);

SELECT *
FROM   YourTable
WHERE  FromTime <= CAST(getDate() AS TIME)
AND    ToTime >= CAST(getDate() AS TIME)
AND  ( 
       (  @DOW = 2 AND Monday = 1 ) 
       OR
       (  @DOW = 3 AND Tuesday = 1 )
       OR
       (  @DOW = 4 AND Wednesday = 1 )
       OR
       (  @DOW = 5 AND Thursday = 1 )
       OR
       (  @DOW = 6 AND Friday = 1 )
)
Monday Tuesday Wednesday Thursday Friday FromTime ToTime
0 0 0 1 1 00:00:00.0000000 23:59:59.0000000

db<>fiddle here

Solution 2:[2]

DECLARE @dotw NVARCHAR(20)
DECLARE @sql NVARCHAR(255)
DECLARE @sqlInsert NVARCHAR(255)

SET @requestDate = '2022-03-03 00:00:00.0000000'
SET @dotw = datename(w, @requestDate)
SET @sql = 'select * from [YourTable] where [YourTable].' + QUOTENAME(@dotw) + ' = 1'  
EXEC sp_executesql @sql

My answer. The only problem here is storing the result of the exec inside a cte.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 SOS
Solution 2 Khen

Related Questions