Why is my CASE expression non-deterministic?

Question

I am trying to create a persisted computed column using CASE expression:

ALTER TABLE dbo.Calendar ADD PreviousDate AS 
case WHEN [Date]>'20100101' THEN  [Date]
    ELSE NULL
    END PERSISTED

MSDN clearly says that CASE is deterministic, here

However, I am getting an error:

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

Of course, i can create a scalar UDF and explicitly declare it as deterministic, but is there a simpler way around this? I am already in the middle of getting the latest service pack. Thanks.

Answer 1

You need to CONVERT '20100101' with a style.

Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified.

So, try this:

...WHEN [Date] > CONVERT(datetime, '20100101', 112)....

Date parsing from string can be unreliable as I've answered before (mostly in comments)

Edit:

I wouldn't say it's a bug, but SQL Server asking for 100% clarification. yyyymmdd is not ISO and SQL Server parsing yyyy-mm-dd is unreliable (see my answer link)

Answer 2

Apparently it is very picky about data types. Try doing this:

ALTER TABLE dbo.Calendar ADD PreviousDate AS 
case WHEN [Date ]> Convert(DateTime, '20100101', 101) THEN  [Date]
    ELSE Convert(DateTime, NULL, 101)
    END PERSISTED