Computed column cannot be persisted because the column is non-deterministic
You need to use a deterministic style when converting from a string representation.
You were not using a deterministic style with the conversion from string to date
.
You were unnecessarily specifying a style when converting from date to datetime2
.
There is a confusing mixture of date/time data types in the question.
This works (producing a datetime
column):
ALTER TABLE dbo.test
ADD UTCTime AS
ISNULL
(
EventTime,
DATEADD
(
SECOND,
PosixTime,
CONVERT(datetime, '19700101', 112)
)
)
PERSISTED;
As Aaron mentioned (we were answering concurrently), you do not need to persist a deterministic column to index it.
Converting a string to a date without a style number is not deterministic, also there is no reason to use a style number when converting a date or datetime to datetime2. Try:
ALTER TABLE dbo.test
ADD UTCTime AS CONVERT(datetime2,ISNULL(EventTime,
DATEADD(SECOND, PosixTime, CONVERT(datetime,'1970-01-01',120))))
PERSISTED;
Though I'm curious why you need to persist this column. If it is so you can index it, you don't need to persist a column to index it...