Persistent Computed column using DATEADD - MSSQL 2005
-
MSSQL complains that a computed columnn is non-deterministic if I use a string constant for a datetime in DATEADD but it works ok if I use a datetime column:
Formula that works: (dateadd(day,([event_date]-2415021),[adatetime]))
Formula that does NOT work:
(dateadd(day,(10),'1900-01-01 00:00:00.000'))
Seems like a bug to me
-
Well technically it's not an unamiguous date - there's a couple of guaranteed dateformats that may work, and you're using a string in place of a date so I can imagine it would complain try converting the string to a date
e.g. (dateadd(day,(10),convert(datetime,'1900-01-01 00:00:00.000')))
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Neither of the following work:
(dateadd(day,(10),convert(datetime,'1900-01-01 00:00:00.000')))
(dateadd(day,(10),cast('1900-01-01 00:00:00.000' as datetime)))
-
I'll get back to you on this one
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
CREATE
TABLE [dbo].[test1] (
[c1] [int]
NULL ,
[c2]
AS (dateadd(day,10,convert(datetime,'1900-01-01 00:00:00.000')))
)
ON [PRIMARY]
GO
works fine for me on 2000 and 2005
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Yes the table can be created this way because the column is not persisted. If you create it as shown below, or modify the column created by your query to be PERSISTED, it gets the error:
"Computed column 'c2' in table 'test1' cannot be persisted because the column is non-deterministic."
Query that fails:
-----------------------------------------------------------------
CREATE
TABLE [dbo].[test1] (
[c1] [int]
NULL ,
[c2]
AS (dateadd(day,10,convert(datetime,'1900-01-01 00:00:00.000'))) PERSISTED
)
ON [PRIMARY]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply