0

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')
E.K.
  • 4,179
  • 8
  • 30
  • 50

1 Answers1

1

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
Ben Thul
  • 31,080
  • 4
  • 45
  • 68