问题描述
我有一个计算列的函数：
CREATE FUNCTION [dbo]。[GetAllocatedStartTime] @Week INT）
RETURNS DATETIME
与模式绑定
AS BEGIN
返回日期add（周，@周 - （1），dateadd（天， ，dateadd（week，datediff（week，（0），CONVERT（[varchar]（4），@ Year，（0））+' - 01-01'），（1））））
END
GO
我添加了 WITH schemabinding 希望它会使其确定性，所以我可以坚持。应该是两个输入 [Week] 和 [Year] 将始终产生相同的结果。
确切的错误是：
我在此列中使用此公式：
（[dbo]。[GetAllocatedStartTime]（[Year]，[Week]））
和列defs：
周] [int] NOT NULL，
[Year] [int] NOT NULL，
[AllocatedTimeStart] AS（[dbo]。[GetAllocatedStartTime] $ b 任何想法？
将行更改为：
week，@ Week-（1），dateadd（day，（ - 1），dateadd（week，datediff（week，（0），CONVERT（datetime，CONVERT（[varchar]（4），@ Year， +'0101'，112）），（1））））
说该列的公式无效。即使该功能保存得很好。
编辑2：
显示我正在做什么（或至少我试过）。没有什么额外的真的。因为它说的以前的函数（原始的）加上公式ref [dbo] .AllocatedStartDate（...）到它在列工作，但不是持久，它说它是非确定性的。因此，根据建议，我改变了FUNCTION，用新代码替换转换部分，所以函数现在看起来像：
FUNCTION [dbo]。[GetSTime]（@ Year INT，@Week INT）
返回DATETIME
与模式绑定
AS BEGIN
返回dateadd - （1），dateadd（day，（ - 1），dateadd（week，datediff（week，（0），CONVERT（datetime，CONVERT（[varchar]（4），@ Year，（0））+'0101' ，112）），（1））））
END
在计算字段（（[dbo]。[GetAllocatedStartTime]（[Year]，[Week]））） ...中的公式，它的无效...这是奇怪的公式是相同的，所以它必须做一些类型的检查的更改的函数，并发现是无效的，这也很奇怪，因为我做了一个简单的 SELECT dbo.GetAllocatedStartTime（2012.13）和它工作...
所以是的，我很困惑， SqlFiddle 不要使用它。
CONVERT（[varchar] 4），@ Year，（0））+' - 01-01'正被传递到 DATEDIFF 日期，强制进行隐式转换。
从：
好吧，你不是调用，但是你依赖于一个隐式转换，我会期望像 CAST 。而不是依赖于此，我将切换到使用 CONVERT 并给出一个确定性的样式参数。
我会做： CONVERT（datetime，CONVERT（[varchar]（4），@ Year，（0））+'0101'，112）这样做后，函数本身变成确定性的
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 :
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
RETURN dateadd(week,@Week-(1),dateadd(day,(-1),dateadd(week,datediff(week,(0),CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112)),(1))))
END
Then I tried the same formula as before in the computed field (([dbo].[GetAllocatedStartTime]([Year],[Week]))) ... and it rejects the formula, says its not valid... which is strange as the formula is the same, so it must be doing some sort of check of the changed function and finding that to be invalid, which is also strange because I did a plain SELECT dbo.GetAllocatedStartTime(2012,13) and it worked...
So yes I am confused, and I've never seen SqlFiddle never mind use it. But really there is nothing more than what I have just said.
CONVERT([varchar](4),@Year,(0))+'-01-01' is being passed to a DATEDIFF call, in a position where a date is expected, forcing an implicit conversion to occur.
From the rules for deterministic functions:
Well, you're calling neither, but you're relying on an implicit conversion, which I'd expect to act like CAST. Rather than rely on this, I'd switch to using CONVERT and give a deterministic style parameter.
So, I'd do: CONVERT(datetime,CONVERT([varchar](4),@Year,(0))+'0101',112) in its place. Having done so, the function itself becomes deterministic
这篇关于不能保留计算列 - 不是确定性的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！