CODEWITHSUNDEEP
sql-serverfunctiondeterministicnon-deterministic
E.K.
E.K.

Reputation: 4349

Convert to a deterministic function with SQL Server

I am trying to create a deterministic function with SQL Server so that I can create an index for a view, which calls this function. This function takes a column name and returns the end of the month as datetime. For example, 201701 to 20170131.

Can you please help me to convert this to a deterministic function?

CREATE FUNCTION dbo.ufnGetFiscalPeriodEndDate (@FiscalPeriod VARCHAR(10))

RETURNS DATE 
WITH SCHEMABINDING
AS BEGIN
    RETURN EOMONTH(CAST(LEFT(@FiscalPeriod, 4) + RIGHT(@FiscalPeriod, 2) + '01' AS DATE))
END

This OBJECTPROPERTY query returns 0...

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.ufnGetFiscalPeriodEndDate'), 'IsDeterministic')

Upvotes: 0

Views: 649

Answers (1)

Ben Thul
Ben Thul

Reputation: 32737

If you've got EOMONTH, you've got DATEFROMPARTS. I think CAST is non-deterministic because it can depend on language settings. But this seems to work for me:

ALTER FUNCTION dbo.ufnGetFiscalPeriodEndDate (@FiscalPeriod VARCHAR(10))

RETURNS DATE 
WITH SCHEMABINDING
AS BEGIN
    RETURN EOMONTH(DATEFROMPARTS(LEFT(@FiscalPeriod, 4), RIGHT(@FiscalPeriod, 2) , 1));
END
go

Upvotes: 1

Related Questions