How can use current day as column name in SQL?-CodePudding

I have a table in SQL Server.

All days exist as columns in the table (Monday, Tuesday, ...).

The data types of the columns are bit.

In this case, how can I check current day is true in where condition?

Like this

select *
from Servis
where [FORMAT(GETDATE(),'dddd')] = 1 --Current day

Thanks in advance!

CodePudding user response：

You can use a CASE in the WHERE clause.

SELECT *
FROM Servis
WHERE CASE DATENAME(weekday, GETDATE()) 
      WHEN 'Monday' THEN Monday
      WHEN 'Tuesday' THEN Tuesday
      WHEN 'Wednesday' THEN Wednesday
      WHEN 'Thursday' THEN Thursday
      WHEN 'Friday' THEN Friday
      WHEN 'Saturday' THEN Saturday
      WHEN 'Sunday' THEN Sunday
      END = 1;

Demo on db<>fiddle here

CodePudding user response：

As Larnu mentioned the design should be changed in order to ease of query. But when you insist to keep this structure; here is the sample:

-- Assuming This is the structure of table
Create Table Servis (
    Saturday bit Default (0), 
    Sunday bit Default (0), 
    Monday bit Default (0), 
    Tuesday bit Default (0), 
    Wednesday bit Default (0), 
    Thursday bit Default (0), 
    Friday bit Default (0)
)
GO

INSERT Servis DEFAULT VALUES
GO

Declare @ColumnName sysname= FORMAT(GETDATE(),'dddd')

DECLARE @Result INT
DECLARE @Params nVARCHAR(4000) = N'@Result INT OUTPUT'
Declare @Query nVarchar(4000) = REPLACE(N'Set @Result = (SELECT @ColumnName FROM Servis)', N'@ColumnName', @ColumnName)

EXEC sp_executesql @stmt = @Query, @params = @Params, @Result = @Result OUTPUT

PRINT @Result