2

According to MS docs DATEADD is a deterministic function hence my function below should be deterministic too:

CREATE FUNCTION [dbo].[Epoch2Date] (@i INT)
RETURNS DATETIME WITH SCHEMABINDING
BEGIN
    RETURN DATEADD(SECOND,@i,'1970-01-01 00:00:00')
END

But when I check it with SELECT OBJECTPROPERTY(OBJECT_ID('[dbo].[Epoch2Date]'), 'IsDeterministic') it returns 0 (Non-deterministic).

  1. Why it is non-deterministic?
  2. How can I make my function deterministic?

There is a similar question but it uses non-deterministic function CAST which is not the case here.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
endo64
  • 2,269
  • 2
  • 27
  • 34

1 Answers1

4

DATEADD is. Implicitly converting a varchar to a datetime, however, is not. This is especially worse when the format you use is ambiguous for datetime (though at least the value would be the same).

You need to explicitly convert the value with a style:

DATEADD(SECOND,@i,CONVERT(datetime,'19700101',112))
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Thanks! That's solved the issue. It would be nice to remark that on the MS docs page. – endo64 Feb 21 '21 at 22:09
  • It is, @endo64 , in the document I quoted in the question you linked. An implicit cast/convert is still a cast/convert. In your attempt you have a character string value (`'1970-01-01 00:00:00'`) you are implicitly casting/converting without a style to a `datetime`; and that is **explicitly** defined as non-deterministic. – Thom A Feb 21 '21 at 22:25
  • I understand, I meant, it would be nice to note that `DATEADD` makes an implicit conversion which leads to be non-deterministic. – endo64 Feb 21 '21 at 22:28
  • 1
    If you feel it isn't accurate, @endo64 , then make a pull request on the document; though i personally think it does cover it (implicitly :) ). – Thom A Feb 21 '21 at 22:30