SQL - 仅从周末选择记录

Question

我需要编写一个 SQL Select 语句来选择 DateCreated 在过去 4 周内落在周末的所有记录。我知道我可以通过像下面这样对选择进行硬编码来做到这一点。但我想知道是否有更简单的方法使用 T-sql 日期函数。

SELECT * FROM audit_table 
WHERE DateCreated BETWEEN '07-31-2010 00:00:00' AND '08-01-2010 23:59:59'
  OR DateCreated BETWEEN '07-24-2010 00:00:00' AND '07-25-2010 23:59:59'
  OR DateCreated BETWEEN '07-17-2010 00:00:00' AND '07-18-2010 23:59:59'
  OR DateCreated BETWEEN '07-10-2010 00:00:00' AND '07-11-2010 23:59:59'

Answer 1

尝试这个

where DateCreated  >= DATEADD(DD, DATEDIFF(dd, 0, DATEADD(WK,-4,GETDATE()))+0, 0)
and datepart(dw,DateCreated) in (1,7) -- Sat and Sun only

[编辑] 查看关于日期优先的评论[/编辑]