始终返回上周周一至周日的数据 [英] Always return data from last week's Monday thru Sunday

问题描述

如何写一个总是返回上周一到上周日数据的sql语句?任何指导表示赞赏.

How to write a sql statement that always returns data from last Monday to the last Sunday? Any guidance is appreciated.

谢谢.

推荐答案

我意识到我的代码太复杂了，所以我改变了我的答案.getdate() 后面的减一会对此进行更正，以返回星期日的最后一个星期一，而不是本周的星期一(如果今天是星期日，则为明天).

I realized my code was too complex, so I changed my answer to this. The minus one after getdate() corrects this to return the last Monday on Sunday instead of this weeks Monday (tomorrow if today is Sunday).

SELECT t.* 
FROM <table> t CROSS JOIN
(SELECT DATEDIFF(week, 0, getdate() - 1) * 7 lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

旧代码

SELECT t.* 
FROM <table> t CROSS JOIN
(SELECT DATEDIFF(day, 0, getdate() - DATEDIFF(day, 0, getdate()) %7) lastmonday) a
WHERE t.yourdate >= a.lastmonday - 7 and yourdate < a.lastmonday

这篇关于始终返回上周周一至周日的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！