4

I am trying to persist that a date is on a weekend in a 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 allow me to do this since the DatePart(dw, date) is a non deterministic function.

Msg 4936, Level 16, State 1, Line 2
Computed column 'IsWeekEnd' in table 'PowerUsage' cannot be persisted because the column is non-deterministic.

But in really July 1st, 2013 is a Monday. So technically it should be deterministic by some method.

The reason T-SQL will not let me use the above is that you can change the starting day of the week using 

SET DATEFIRST

I then thought that maybe the number of days between dates would work but I also got the same issue!

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

Is there some other way of doing this?

Chris

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Chris Crowe
  • 333
  • 1
  • 9

1 Answers1

5

DATEDIFF itself is deterministic. You can use 39081 instead if '20070101' since the implicit conversion from a string seems to be seen as non-deterministic. 

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

39081 from

Select CAST(Cast('20070101' as DateTime) as int)
bummi
  • 27,123
  • 14
  • 62
  • 101