Why is this SQL Function TicksToDateTime non-deterministic? and how to make it deterministic?

Question

I have the following function "TicksToDateTime"

CREATE FUNCTION [dbo].[TicksToDateTime] (@t bigint)
RETURNS datetime
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstickstopic.asp
BEGIN 
    declare @result datetime
    if (@t=0)
        set @result = null
    else
        if (@t < 552877919999983334)
            set @result = cast ('1753-1-1' as datetime)
        else
            if (@t=3155378975999999999)
                set @result = cast ('9999-12-1' as datetime)
            else
                set @result = CAST((@t - 599266080000000000) / 10000000 / 24 / 60 / 60 AS datetime)
    return @result
END
GO

and the use it in a computed column of a table:

[CallDateRaw] BIGINT NOT NULL,
[CallDate] AS ([dbo].[TicksToDateTime]([CallDateRaw])),

I am now trying to index the "CallDate" column like so:

Create Index ExternalCalls_CallDate2 ON [External.Call] (CallDate)
GO

But the index fails because the column is "non-deterministic" and I can confirm that the function is also non-deterministic with:

select object_id('tickstodatetime')

select OBJECTPROPERTYEX(2127346643, 'IsDeterministic')

Which returns false..

So my question is why is this function "non-deterministic" and how do I make it deterministic? from what I read on the internet it just said add "With SchemaBinding" but as you can see I've added that and it still doesnt work.

What am I doing wrong?

Using `CAST` to/from a date and time data type is not deterministic. — Thom A, Jul 15 '20 at 11:28
@Larnu thanks that gives a bit more information, so is it a case of changing all of the `Casts` to `Converts`? — JKennedy, Jul 15 '20 at 11:28

Thom A · Accepted Answer · 2020-07-15T11:44:28.910

Using CAST to/from the old date and time data types is not deterministic. Using CONVERT to/from the old date and time data types can be deterministic, for example if using a style code that is deterministic or not from a character string. You are using CAST here, so it isn't.

From Deterministic and Nondeterministic Functions:

The following functions are not always deterministic, but can be used in indexed views or indexes on computed columns when they are specified in a deterministic manner.

Function                   Comments
all aggregate functions    All aggregate functions are deterministic unless they are specified with the    OVER and ORDER BY clauses. For a list of these functions, see Aggregate Functions (Transact-SQL).

CAST                       Deterministic unless used with datetime, smalldatetime, or sql_variant.

CONVERT                    Deterministic unless one of these conditions exists:
                           Source type is sql_variant.
                           Target type is sql_variant and its source type is nondeterministic.
                           Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. 
                           Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

In this case, you need to use CONVERT and a style code. So for example, instead of:

set @result = cast ('1753-1-1' as datetime)

You would need to do:

SET @Result = CONVERT(datetime, '17530101',112);

And a little more from the documentation, with examples: [Nondeterministic conversion of literal date strings into DATE values](https://learn.microsoft.com/en-us/sql/t-sql/data-types/nondeterministic-convert-date-literals) — Eric Brandt, Jul 15 '20 at 11:34
Thanks, that makes sense. I'm a bit stumped on the last line, converting ticks usng cast instead of convert. This line `CAST((@t - 599266080000000000) / 10000000 / 24 / 60 / 60 AS datetime)` — JKennedy, Jul 15 '20 at 11:35
`bigint` to `datetime` is deterministic in `CONVERT` @user1, as the quoted documentation shows. — Thom A, Jul 15 '20 at 11:36
Yep.. I did solve it, I think its just `Convert(datetime,(@t - 599266080000000000) / 10000000 / 24 / 60 / 60)` — JKennedy, Jul 15 '20 at 11:37

JKennedy · Answer 2 · 2020-07-15T11:47:38.577

Just as an extension to @Larnu's excellent answer:

the function I ended up with was:

Create FUNCTION [dbo].[TicksToDateTime] (@t bigint)
RETURNS datetime
WITH SCHEMABINDING
AS
-- converts the given datetime to .NET-compatible ticks
-- see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstickstopic.asp
BEGIN 
    declare @result datetime
    if (@t=0)
        set @result = null
    else
        if (@t < 552877919999983334)
            set @result = Convert(datetime,'17530101',112)
        else
            if (@t=3155378975999999999)
                set @result = Convert(datetime,'99991201',112)
            else
                set @result = Convert(datetime,(@t - 599266080000000000) / 10000000 / 24 / 60 / 60)
    return @result
END


GO

and verified through

select object_id('TicksToDateTime')
select OBJECTPROPERTYEX(11863109, 'IsDeterministic')

FYI, `'9999121'` should be `'99991201'`. – Thom A Jul 15 '20 at 11:41 — Thom A, Jul 15 '20 at 11:41

Why is this SQL Function TicksToDateTime non-deterministic? and how to make it deterministic?

2 Answers2

Linked