How to get a Deterministic computed value for Date + Time

Question

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

What are the data types of the other two columns? The second version should work with `date` and `time` respectively http://sqlfiddle.com/#!18/0d84b — Charlieface, Jul 11 '21 at 16:18
Well then how about change `time` column to a sensible datatype, like maybe `time`?? — Charlieface, Jul 11 '21 at 16:26
@Charlieface The date is a ```datetime``` and the time is a ```varchar```. — Brandon, Jul 11 '21 at 16:26
@Charlieface That's a bigger change than what I was hoping to make, though it makes sense. I didn't build the table, so I'm well aware that's ideal. I suppose the reason it's non-deterministic as a varchar is because it's having to cast it to a time huh? — Brandon, Jul 11 '21 at 16:27
I was able to get it to work if I manually used ```CONVERT``` on the time field. Interesting. — Brandon, Jul 11 '21 at 16:29

Charlieface · Accepted Answer · 2021-07-11T16:39:03.930

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;

How to get a Deterministic computed value for Date + Time

1 Answers1