Search code examples
sql-serverfunctiondeterministic

Why is my SQL function non-deterministic, when it shouldn't be?

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.

Solution

  • 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))