英文:
Why is DATEPART(weekday, GETDATE()) not working for Saturday?
问题
我有一个自动化流程，不希望它在星期六和星期日运行。由于我可以通过 Microsoft SQL Server 中的 WHERE 条件来决定启动流程的对象，我考虑分配以下 WHERE 条件：
[...]
WHERE DATEPART(weekday, GETDATE()) <> 1 -- 不是星期日
AND DATEPART(weekday, GETDATE()) <> 7 -- 不是星期六
AND [其他条件]
英文:
I have an automatic process and I don't want it to run on Saturday and on Sunday. Since I'm able to decide for whom to start the process through a WHERE condition in Microsoft SQL Server, I thought about assigning this WHERE condition:
[...]
WHERE DATEPART(weekday, GETDATE()) <> 1 -- No Sunday
AND DATEPART(weekday, GETDATE()) <> 7 -- No Saturday
AND [other conditions]
Anyway, for some reason this process didn't start on Sunday, but it started on Saturday. What am I missing?
答案1
得分: 1
正如在DATEPART中所记录的那样，
> 对于一周（wk，ww）或工作日（dw）日期部分，DATEPART的返回值取决于由SET DATEFIRST设置的值。
DATEFIRST的默认值是从登录语言中派生的。由于您显然只能更改WHERE子句，因此您可能无法明确设置它。您可以通过运行作业来更改登录的默认语言，或者使用@@DATEFIRST进行一些神秘的算术运算，使其不依赖于该设置。
或者，您可以考虑像这样做：
WHERE FORMAT(GETDATE(), 'dddd', 'en-GB') NOT IN ('Saturday', 'Sunday')
FORMAT调用CLR，因此在微基准测试中可能会有一些性能影响。希望该表达式只会被评估一次，而不是在每一行（可能在带有启动谓词的过滤器中）中重新评估。
即使在最坏的情况下，它被评估了15K次，这可能也不会比一天执行一次的东西使用的额外双位数毫秒的CPU时间更差 - 我相信您有比那更重要的性能问题需要解决。
英文:
As is documented in DATEPART
> For a week (wk, ww) or weekday (dw) datepart, the DATEPART return
> value depends on the value set by SET DATEFIRST.
The default value for DATEFIRST is in turn derived from the language of the login. As you are apparently restricted to just changing the WHERE clause you presumably can't set that explicitly. You can change the default language of the login running the job or do some cryptic arithmetic with @@DATEFIRST to make it agnostic to that.
Or you could consider doing something like
WHERE FORMAT(GETDATE(), 'dddd', 'en-GB') NOT IN ('Saturday', 'Sunday')
FORMAT calls out to the CLR so does have some potential performance impact visible in micro benchmarks. Hopefully the expression will only be evaluated once though rather than re-evaluated for each row (potentially in a filter with a startup predicate).
And even in the worst case if it is evaluated 15K times this is likely to be no worse than an extra double digit ms of CPU time used by something that executes once a day (?) - I'm sure you will have much bigger performance fish to fry than that.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论