CODEWITHSUNDEEP
sqlsql-servercalculated-columns
Brandon
Brandon

Reputation: 3276

How to get a Deterministic computed value for Date + Time

I have a table that has a datetime and a time field where the datetime is actually just the date and the time field is the time. I need to combine these 2 fields to create a "created_time" computed field. I'd like to persist this computed field but I keep getting a "non-deterministic" error when I'm trying to combine them. Firstly, I'm not sure I understand why it's non-determinstic, but secondly, is there any fancy way to get around that and accomplish this?

--------------------------------------------------------
       date (datetime)           |       time (varchar)
--------------------------------------------------------
2021-05-05 00:00:00              | 12:00
2021-05-13 00:00:00              | 18:01

I've tried using the following computations and neither work.

ALTER TABLE dbo.table ADD created_time AS ([date] + [time]) PERSISTED

ALTER TABLE dbo.table ADD created_time AS (DATEADD(millisecond, DATEDIFF(millisecond, 0, [time]), [date])) PERSISTED

Upvotes: 3

Views: 808

Answers (1)

Charlieface
Charlieface

Reputation: 72194

Firstly, the + operator is not valid for date and time values, strange as it may seem. So you need to use your second version.

Secondly, your time column is actually varchar, so the conversion is an implicit non-deterministic conversion.

You can make it deterministic by using CONVERT with a deterministic style parameter, such as 108 in this case:

ALTER TABLE dbo.[table] ADD created_time AS
  (DATEADD(millisecond,
      DATEDIFF(millisecond, 0, CONVERT(time, [time], 108)),
  [date])) PERSISTED;

This is documented here, some style parameters are not deterministic, because it can depend on culture and century (where year is two-digits).

I strongly suggest you either convert your time column into the time data type, or better yet, change it to a combined datetime column:

ALTER TABLE dbo.[table]
  ALTER COLUMN [time] time NOT NULL;

-- or
ALTER TABLE dbo.[table] ADD created_time datetime NULL;

UPDATE dbo.[table]
SET created_time = (DATEADD(millisecond,
      DATEDIFF(millisecond, 0, CONVERT(time, [time], 108)),
  [date]));

ALTER TABLE dbo.[table]
  DROP COLUMN [time]
  DROP COLUMN [date];

ALTER TABLE dbo.[table] ALTER created_time datetime NOT NULL;

Upvotes: 2

Related Questions