Deterministic non-nullable truncate function for datetime

Question

I was looking for a deterministic truncate function for datetime and this one did the job:

DATEADD(dd, DATEDIFF(dd, 0, @date), 0)

But this is supposed to be the input to a persisted computed column which will be a part of the primary key, so it has to be non-nullable. So I made this:

ISNULL(DATEADD(dd, DATEDIFF(dd, 0, @date), 0), '01.01.1900')

But now the expression became non-deterministic. Can anyone tell me why, and how I can make it deterministic?

Thanks!

Answer 1

01.01.1900 isn't deterministic...

This is:

ISNULL(DATEADD(dd, DATEDIFF(dd, 0, @date), 0), CONVERT(datetime, '19001010', 112)))

or this

ISNULL(DATEADD(dd, DATEDIFF(dd, 0, fooDT), 0), 0)

For more, see Why is my CASE expression non-deterministic? which leads to best way to convert and validate a date string