How to save bit field indicating date on weekend?

Question

I try to have the date on the weekend in the table.

ADD [IsWeekEnd] AS (CASE DATEPART(dw, GETDATE()) WHEN 1 THEN 1 WHEN 6 THEN 1 ELSE 0 END) PERSISTED ADD [IsWeekEnd] AS (CASE SUBSTRING(DATENAME(dw, GETDATE()), 1, 3) WHEN 'Sun' THEN 1 WHEN 'Sat' THEN 1 ELSE 0 END) PERSISTED 

T-SQL will not let me do this since DatePart(dw, date) is a non-deterministic function.

Msg 4936, Level 16, State 1, Line 2
The calculated IsWeekEnd column in the PowerUsage table cannot be saved because the column is not deterministic.

But in fact, July 1, 2013 is Monday. So technically this has to be determined by some method.

The reason T-SQL won't let me use the above is because you can change the start day of the week using

 SET DATEFIRST 

Then I thought that maybe the number of days between dates would work, but I also have the same problem!

 ADD [IsWeekEnd] AS (CASE WHEN DATEDIFF(DAY, '20070101', [datetime]) % 7 > 4 THEN 1 ELSE 0 END) PERSISTED 

Is there any other way to do this?

Chris

Answer 1

DATEDIFF itself is deterministic. Instead, you can use 39081 if "20070101", since the implicit conversion from a string appears to be considered non-deterministic.

 ADD [IsWeekEnd] AS (CASE WHEN DATEDIFF(DAY, 39081, [datetime]) % 7 > 4 THEN 1 ELSE 0 END) PERSISTED 

39,081 of

 Select CAST(Cast('20070101' as DateTime) as int)