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...