我使用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。