4

I want to make a UDF which returns an integer form of YYYYMM so that I can easily partition some things on month. I am trying to assign this function to the value of a PERSISTED computed column.

I currently have the following, which works fine:

CREATE FUNCTION dbo.GetYearMonth(@pDate DATETIME2)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @fYear VARCHAR(4) = RIGHT('0000' + CAST(YEAR(@pDate) AS VARCHAR),4)
    DECLARE @fMonth VARCHAR(2) = RIGHT('00' + CAST(MONTH(@pDate) AS VARCHAR),2)

    RETURN CAST(@fYear + @fMonth AS INT)
END

But I think it's cleaner to use FORMAT instead. I tried this:

CREATE FUNCTION dbo.GetYearMonth(@pDate DATETIME2)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @fYear VARCHAR(4) = FORMAT(@pDate,'yyyy', 'en-us')
    DECLARE @fMonth VARCHAR(2) = FORMAT(@pDate,'MM', 'en-us')

    RETURN CAST(@fYear + @fMonth AS INT)
END

But this function is nondeterministic. Is there a way to make FORMAT deterministic? Or is there a better way to do this, making the UDF deterministic?

Vadim Ovchinnikov
  • 13,327
  • 5
  • 62
  • 90
Matthew
  • 10,244
  • 5
  • 49
  • 104

3 Answers3

7

Interesting question so I did some digging and came up with nothing conclusive :)

Starting with Deterministic and Nondeterministic Functions

which doesn't explicitly list FORMAT but states:

All of the aggregate and string built-in functions are deterministic.

and links to String Functions

Again, this page states

All built-in string functions are deterministic. This means they return the same value any time they are called with a specific set of input values.

However, the page on FORMAT is silent on the subject.

FORMAT uses the CLR which doesn't preclude it from being deterministic but the doco is silent as to the actual implementation of FORMAT.

Finally, suspecting this is a bug in the doco (or the code) a quick search of connect reveals nothing.

How about a test case? (Comments welcome on the validity of this code…)

CREATE FUNCTION WhatAmI(@Number INTEGER) 
RETURNS NVARCHAR
WITH SCHEMABINDING
AS 
BEGIN
RETURN FORMAT(@Number, 'd')
END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('WhatAmI'),'IsDeterministic')

-----------
0

(1 row(s) affected)

Nup, that's nondeterministic.

So, an interesting exercise but nothing conclusive.

So what did we learn (according to BOL)? If a built-in function is nondeterministic, there's no way of making it so. Some are both depending on the parameter types, and some are supposed to be but aren't.

Vadim Ovchinnikov
  • 13,327
  • 5
  • 62
  • 90
Liesel
  • 2,929
  • 2
  • 12
  • 18
  • Please [vote on fixing this bug](https://feedback.azure.com/forums/908035-sql-server/suggestions/34621960-make-built-in-format-function-deterministic). – Vadim Ovchinnikov Jun 21 '18 at 09:37
5

I'm wondering why you are using a function. You can use a simple formula: 

CONVERT(varchar(6), getdate(), 112)
FLICKER
  • 6,439
  • 4
  • 45
  • 75
  • because I didn't know I could do that – Matthew Jun 03 '16 at 20:21
  • 1
    This comment gives me what I was looking for but doesn't specifically answer the question so I upvoted but didn't accept. – Matthew Jun 03 '16 at 20:42
  • +1. Specifically, style 112 (ISO) is deterministic. Some styles are deterministic and some are not, according to the [docs](https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017#certain-datetime-conversions-are-nondeterministic). – Branko Dimitrijevic Jul 05 '18 at 12:50
1

Microsoft isn't very clear about the format function being able to be determinstic. I couldn't find any info on the subject in its documentation. Being a CLR function i guess the awnser is it is not possible.

An even more simple solution to get an integer formatted the way you want would be:

YEAR(@pDate) * 100 + MONTH(@pDate)
MWillemse
  • 960
  • 5
  • 9
  • 1
    This comment gives me what I was looking for but doesn't specifically answer the question so I upvoted but didn't accept. – Matthew Jun 03 '16 at 20:42