我使用
DATEDIFF功能来过滤本周添加的记录：
DATEDIFF(week,DateCreated,GETDATE()) = 0
我注意到周日从星期天开始是什么假设.但在我的情况下,我宁愿在星期一设定一周的开始.在T-sql中有可能吗？
谢谢！
更新：
下面是一个示例,显示DATEDIFF不检查@@DATEFIRST变量,所以我需要另一个解决方案.
SET DATEFIRST 1;
SELECT
DateCreated,DATEDIFF(week,CAST('20090725' AS DATETIME)) AS D25,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.
解决方法
有可能吗
SET DATEFIRST 1; -- Monday
从http://msdn.microsoft.com/en-us/library/ms181598.aspx
看来dateiff不尊重Datefirst,所以就这样运行它
create table #testDates (id int identity(1,1),dateAdded datetime)
insert into #testDates values ('2009-07-09 15:41:39.510') -- thu
insert into #testDates values ('2009-07-06 15:41:39.510') -- mon
insert into #testDates values ('2009-07-05 15:41:39.510') -- sun
insert into #testDates values ('2009-07-04 15:41:39.510') -- sat
SET DATEFIRST 7 -- Sunday (Default
select * from #testdates where datediff(ww,DATEADD(dd,-@@datefirst,dateadded),getdate())) = 0
SET DATEFIRST 1 -- Monday
select * from #testdates where datediff(ww,getdate())) = 0
被盗从
