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!

@Andriy M's answer is correct and before mine. You should accept that — gbn, Feb 24 '12 at 15:44

score 6 · Accepted Answer · edited May 23 '17 at 10:34

6

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

edited May 23 '17 at 10:34

Community

1
1

answered Feb 24 '12 at 15:13

gbn

422,506
82
585
676

score 4 · Answer 2 · edited Feb 24 '12 at 15:38

4

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

That is, my guess is the non-deterministic part is '01.01.1900', because it depends on the locale settings.

edited Feb 24 '12 at 15:38

gbn

422,506
82
585
676

answered Feb 24 '12 at 15:11

Andriy M

76,112
17
94
154

@gbn, Martin Smith, JNK: Thanks, guys. – Andriy M Feb 24 '12 at 15:40

score 0 · Answer 3 · answered Feb 24 '12 at 15:13

0

Don't use string constant, use some arbitrary number instead of '01.01.1900'

answered Feb 24 '12 at 15:13

Oleg Dok

21,109
4
45
54

Deterministic non-nullable truncate function for datetime

3 Answers3