Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel