CODEWITHSUNDEEP
sqlsql-servert-sqlcalculated-columns
metric
metric

Reputation: 303

Why is CONVERT(TIME(7), expression) not deterministic?

I have an existing table I would like to add a computed column

[dbo].[Route]
   ...
   [EstimatedArrival] TIME (7) NOT NULL,
   [DriveSeconds]     INT      NOT NULL,
   [WaitSeconds]      INT      NOT NULL,
   ...

But when I tried to add computed column of TIME below

ALTER TABLE [dbo].[Route]
  ADD [EstimatedDeparture] AS 
  CONVERT (TIME (7), DATEADD(SECOND, 
    (((DATEPART(HOUR, [EstimatedArrival]) * 3600) 
     + (DATEPART(MINUTE, [EstimatedArrival]) * 60)  
     + DATEPART(SECOND, [EstimatedArrival])) 
     - [DriveSeconds] - [WaitSeconds]), ''), 114) 
  PERSISTED;

it throws

Computed column 'EstimatedDeparture' in table 'Route' cannot be persisted because the column is non-deterministic.

Why? I thought CONVERT(TIME (7) ...) should guarantee the column type as TIME

Note that if I use this Convert expression in query, it works fine. How do I fix this problem?

Upvotes: 0

Views: 204

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

You can correct and simplify it this way:

ALTER TABLE [dbo].[Route]
ADD [EstimatedDeparture] AS 
CONVERT (TIME (7), DATEADD(SECOND, - [DriveSeconds] - [WaitSeconds], [EstimatedArrival]))
PERSISTED;

Upvotes: 1

Aaron Bertrand
Aaron Bertrand

Reputation: 280500

What is this empty string supposed to represent?

- [DriveSeconds] - [WaitSeconds]), ''), 114) 
-----------------------------------^^

This likely tells SQL Server you're going to be interpreting part of this as a string. While I agree with HABO that you don't need the 114 style either, I was able to make this work using the following, which still uses the unnecessary style number:

- [DriveSeconds] - [WaitSeconds]), 0), 114)

Whether that still retains the proper calculation, I'm not sure, since I don't know what the intent is, but avoiding implicit conversions to strings here should avoid the problem.

Upvotes: 2

Related Questions