是否可以为 T-SQL DATEDIFF 函数设置一周的开始?
我使用 DATEDIFF 函数仅过滤本周添加的记录:
DATEDIFF(week, DateCreated, GETDATE()) = 0
我注意到假设哪周从星期日开始。 但就我而言,我更愿意将一周的开始时间设置为星期一。 在 T-SQL 中是否有可能?
谢谢!
更新:
下面的示例显示了 DATEDIFF 不检查的内容@@DATEFIRST 变量,所以我需要另一个解决方案。
SET DATEFIRST 1;
SELECT
DateCreated,
DATEDIFF(week, DateCreated, CAST('20090725' AS DATETIME)) AS D25,
DATEDIFF(week, DateCreated, CAST('20090726' AS DATETIME)) AS D26
FROM
(
SELECT CAST('20090724' AS DATETIME) AS DateCreated
UNION
SELECT CAST('20090725' AS DATETIME) AS DateCreated
) AS T
输出:
DateCreated D25 D26
----------------------- ----------- -----------
2009-07-24 00:00:00.000 0 1
2009-07-25 00:00:00.000 0 1
(2 row(s) affected)
2009 年 7 月 26 日是星期日,我希望 DATEDIFF 在第三列中也返回 0。
I use DATEDIFF function to filter records added this week only:
DATEDIFF(week, DateCreated, GETDATE()) = 0
and I noticed what it's assumed what week starts on Sunday. But in my case I would prefer to set start of week on Monday. Is it possible somehow in T-SQL?
Thanks!
Update:
Below is an example showing what DATEDIFF doesn't check @@DATEFIRST variable so I need another solution.
SET DATEFIRST 1;
SELECT
DateCreated,
DATEDIFF(week, DateCreated, CAST('20090725' AS DATETIME)) AS D25,
DATEDIFF(week, DateCreated, CAST('20090726' AS DATETIME)) AS D26
FROM
(
SELECT CAST('20090724' AS DATETIME) AS DateCreated
UNION
SELECT CAST('20090725' AS DATETIME) AS DateCreated
) AS T
Output:
DateCreated D25 D26
----------------------- ----------- -----------
2009-07-24 00:00:00.000 0 1
2009-07-25 00:00:00.000 0 1
(2 row(s) affected)
26 Jul 2009 is Sunday, and I want DATEDIFF returns 0 in third column too.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
是的,可以
从 http://msdn.microsoft.com/en-us/library /ms181598.aspx
看来 datediff 不尊重 Datefirst,所以让它像这样运行它
从
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8cc3493a-7ae5-4759- ab2a-e7683165320b
Yes it possible
from http://msdn.microsoft.com/en-us/library/ms181598.aspx
It appears datediff doesn't respect the Datefirst, so make it do so run it like this
Stolen from
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/8cc3493a-7ae5-4759-ab2a-e7683165320b
我有另一个解决方案。
这应该更容易理解,如果我错了请纠正我
我们从日期中减去“-1”,星期日将成为星期六(这是一周的第七天)
Mondфy(2) 是一周的第一天
I have another solution.
This should be easier to understand, correct me if I am wrong
We subtract '-1' from date and Sunday will become Saturday (which is 7nth day of week)
and Mondфy(2) will first day of week
所以如果我理解正确的话
我们唯一需要做的就是从
datediff
的两个日期中删除 1 天,如下所示:So if i'm getting this correctly,
the only thing we need to do is remove 1 day from both dates on our
datediff
as following :