CODEWITHSUNDEEP
sqlsql-serverdatabase
Roger Nieto
Roger Nieto

Reputation: 61

Convert Computed Column to Persistent

I have an issue, I'm trying to alter some tables in my database by adding a datetimeoffset column with UTC DateTime Zone, this column has to be calculated depending on the date and hour that's been inserted.

This is my code right now:

DATETIMEFROMPARTS(YEAR(FECHA), MONTH(FECHA), DAY(FECHA), HORA , 0, 0, 0)  
AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'UTC' ) PERSISTED

The error I'm getting is this:

Computed column cannot be persisted because the column is non-deterministic.

Upvotes: 1

Views: 620

Answers (1)

Greg
Greg

Reputation: 4055

AT TIME ZONE is nondeterministic.

Since some information (such as timezone rules) is maintained outside of SQL Server and are subject to occasional change, the AT TIME ZONE function is classed as nondeterministic.

Because the time zone is dependent on the OS, if someone changes the time zone, then your data gets out of whack.

Upvotes: 4

Related Questions