6

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
A-K
  • 16,804
  • 8
  • 54
  • 74
  • Old question, I know, but how do you propose to *"create a scalar UDF and explicitly declare it as deterministic"* in SQL Server. I'm told you can do so in Oracle, but I've never seen a way in SQL Server... – jimbobmcgee Mar 31 '16 at 12:38

2 Answers2

18

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)

Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
6

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
ChaosPandion
  • 77,506
  • 18
  • 119
  • 157
  • Unfortunately i cannot mark both replies as answers, so I marked only the first one, and upvoted yours. Thanks! – A-K Aug 30 '10 at 00:57