I have this function for a computed column :

CREATE FUNCTION [dbo].[GetAllocatedStartTime](@Year INT, @Week INT)
RETURNS DATETIME

WITH schemabinding
AS BEGIN
    RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT([varchar](4),@Year,(0))+'-01-01'),(1))))
END

GO

I added the WITH schemabinding in the hope it would make it deterministic so I can persist it. It should be as the two inputs [Week] and [Year] will always yield the same results.

The exact error is :

Computed column 'AllocatedTimeStart' in table 'Tmp_Bookings' cannot be persisted because the column is non-deterministic.

I am using this formula in the column :

([dbo].[GetAllocatedStartTime]([Year],[Week]))

And the column defs :

[Week] [int] NOT NULL,
[Year] [int] NOT NULL,
[AllocatedTimeStart]  AS ([dbo].[GetAllocatedStartTime]([Year],[Week])),

Any ideas?

EDIT:

Changed line to :

RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)),(1))))

But now I get an error saying the formula for the column is invalid. Even though the function saves fine.

EDIT 2:

I've shown exactly what I am doing (or atleast I've tried). There is nothing extra really. As it says the previous function (original one) coupled with the formula ref [dbo].AllocatedStartDate(...) to it in the column worked, but was not persisting, it said it was non deterministic. So according to the suggestion I changed the FUNCTION, replacing the conversion part with the new code, so the function now looks like :

FUNCTION [dbo].[GetSTime](@Year INT, @Week INT)

RETURNS DATETIME
WITH schemabinding
AS BEGIN
    R
Edit
Report