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:

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:

*edit: removed image

CodePudding user response：

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

db<>fiddle here

CodePudding user response：

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.