使 UDF 独立于 DATEFIRST 设置
在SO答案的帮助下，我创建了一个SQL Server UDF（如下），它返回下一个工作日（工作日），给定日期和要添加的天数。
例如，如果日期是星期五，而您想要添加一天，则返回值是下星期一的日期。
这依赖于假设 @@DATEFIRST 设置为 1（星期一），因为我们无法在 UDF 中显式设置 DATEFIRST。
我的问题是，我想在多台服务器上部署此功能，但它们具有不同的 @@DATEFIRST 设置，并且出于生产、测试和开发服务器之间的维护目的，我宁愿只拥有一个功能无需担心正在使用哪个 @@DATEFIRST 设置即可工作。我已经花了很长时间想知道为什么我在不同的环境中得到不同的结果！
如果答案只是“使用存储过程”那么就足够了。 但如果有人可以建议如何重构以下内容以独立于 DATEFIRST 设置，那就太好了。重要的一行可能是 if DatePart(dw, @toDate) not in (6, 7)...
函数定义：
CREATE FUNCTION [dbo].[fn_AddBusinessDays]
(
@fromDate datetime,
@daysToAdd int
)
RETURNS datetime
AS
BEGIN
DECLARE @toDate datetime
DECLARE @daysAdded integer
-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate
while @daysAdded <= @daysToAdd
begin
-- add a day to the to date
set @toDate = DateAdd(day, 1, @toDate)
-- only move on a day if we've hit a week day
if DatePart(dw, @toDate) not in (6, 7)
begin
set @daysAdded = @daysAdded + 1
end
end
RETURN @toDate
END
With the help of answers on SO, I have created a SQL Server UDF (below) which returns the next working day (weekday), given a date and a number of days to add.
For example, if the date is a Friday and you wnat to add one day, the return value is that of the following Monday.
This relies on assuming that @@DATEFIRST is set to 1 (Monday), because we can't explicitly set DATEFIRST in a UDF.
My problem is that I'd like to deploy this function on several servers but they have different @@DATEFIRST settings and for maintenance purposes between Production, Test and Dev servers I'd rather just have one function that worked without having to worry which @@DATEFIRST setting is in use. I've already spent too long wondering why I was getting different results in different environments!
If the answer is simply "use a Stored Proc" then fair enough.
But if anyone can suggest how to refactor the following to be independent of the DATEFIRST setting then that would be great. The significant line is probably going to be if DatePart(dw, @toDate) not in (6, 7)...
Function definition:
CREATE FUNCTION [dbo].[fn_AddBusinessDays]
(
@fromDate datetime,
@daysToAdd int
)
RETURNS datetime
AS
BEGIN
DECLARE @toDate datetime
DECLARE @daysAdded integer
-- add the days, ignoring weekends (i.e. add working days)
set @daysAdded = 1
set @toDate = @fromDate
while @daysAdded <= @daysToAdd
begin
-- add a day to the to date
set @toDate = DateAdd(day, 1, @toDate)
-- only move on a day if we've hit a week day
if DatePart(dw, @toDate) not in (6, 7)
begin
set @daysAdded = @daysAdded + 1
end
end
RETURN @toDate
END
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
IF (@@DATEFIRST + DATEPART(DW, @toDate)) % 7 not in (0,1)应该适用于我认为的所有可能的DATEFIRST值。无论如何，我可能会使用辅助日历表而不是循环 UDF。
IF (@@DATEFIRST + DATEPART(DW, @toDate)) % 7 not in (0,1)should work for all possibleDATEFIRSTvalues I think.I'd probably use an Auxilliary Calendar table for this anyway rather than a looping UDF.
如何使用星期几的名称（不受
@@datefirst影响）：作为马丁很好指出的警告；
How about using the names of the week days (which are not be affected by
@@datefirst):As a caveat as martin well points out;