SQL - Only select records from weekends

Question

I need to write a SQL Select statement to select all the records where DateCreated falls on a weekend for the last 4 weeks. I know I can do this by hard-coding the select like the one below. But I was wondering if there was an easier way using T-sql date functions.

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

try this

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

[Edit] see comment about datefirst[/Edit]

Answer 2

Check out the second answer on GETDATE last month. It has a pretty robust rundown of different ways to get dates out of SQL.

Specifically he lists "dateadd(ms, -3, dateadd(wk, datediff(wk, 0, getdate()) + 1, 0))" as a way to get this weekend. This can easily be translated to get the weekends that you need.