I answered this question today and thought it was interesting enough for a blog post.
The easiest way to return all rows that are not older than 4 weeks and fall on a wekeend would be to use that handy calendar table that you have.
Of course not everyone has a calendar table so here is a way to do it without.

First create this table

T-SQL
1
2
3
4
CREATE TABLE TestData (id INT NOT NULL PRIMARY KEY, 
            SomeData VARCHAR(36) NOT NULL, 
            SomeDate DATETIME NOT NULL)
GO
CREATE TABLE TestData (id INT NOT NULL PRIMARY KEY, 
			SomeData VARCHAR(36) NOT NULL, 
			SomeDate DATETIME NOT NULL)
GO

Now we have to insert some data, the query below will insert 2048 rows into the table

T-SQL
1
2
3
4
INSERT TestData
SELECT number, NEWID(), DATEADD(hh,- number,GETDATE())
FROM master..spt_values
WHERE TYPE = 'P'
INSERT TestData
SELECT number, NEWID(), DATEADD(hh,- number,GETDATE())
FROM master..spt_values
WHERE TYPE = 'P'

The rows inserted will look something like this

0	286D95F8-FD33-4903-B3D2-644BC4AD88EE	2010-08-06 12:28:09.913
1	8C985938-085B-482F-A6C8-678954ADF3DA	2010-08-06 11:28:09.913
2	46437AF4-CF1C-4DF3-B9DA-36153B3844CD	2010-08-06 10:28:09.913
3	B5E15934-3F94-4543-9A6F-9C78BEEBB588	2010-08-06 09:28:09.913
4	9655A196-C434-4322-A9DC-14E676E45E25	2010-08-06 08:28:09.913
-----
-----
-----
2046	1710469B-0545-4719-95FF-B87570EC07C3	2010-05-13 06:28:09.913
2047	EF306E11-10F5-40C5-B2BC-862CAC5D5CBD	2010-05-13 05:28:09.913

So what we did is take the getdate() value and then subtract one hour from it for each row

Next up is creating an index on the SomeDate column

T-SQL
1
2
CREATE  INDEX ix_SomeDate ON TestData(SomeDate)
GO
CREATE  INDEX ix_SomeDate ON TestData(SomeDate)
GO

Now we are ready to write our query. How do we grab all the data that is not older than 4 weeks?
We can use the following for that, if you are not on SQL Server 2008 or above use DATEADD(wk, DATEDIFF(wk, 0, GETDATE())-4, 0). If you are on SQL Server 2008 or above you can use CONVERT(DATE,DATEADD(WK,-4,GETDATE()))

If you run that today on August 8 2010 it returns 2010-07-09 00:00:00.000
Try it yourself

T-SQL
1
2
3
SELECT CONVERT(DATE,DATEADD(WK,-4,GETDATE()))
 
SELECT DATEADD(DD, DATEDIFF(dd, 0, DATEADD(WK,-4,GETDATE()))+0, 0)
SELECT CONVERT(DATE,DATEADD(WK,-4,GETDATE()))

SELECT DATEADD(DD, DATEDIFF(dd, 0, DATEADD(WK,-4,GETDATE()))+0, 0)

Now we have to do the weekend part, in the US the start of the week is Sunday, if you check @@DATEFIRST it should return a 7

T-SQL
1
SELECT @@DATEFIRST
SELECT @@DATEFIRST

In Holland for example the week start on Saturday, run this to see what @@DATEFIRST returns for a different language

T-SQL
1
2
3
4
5
6
7
SET LANGUAGE Dutch;
GO
SELECT @@DATEFIRST;  --1
GO
SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;  --7
SET LANGUAGE Dutch;
GO
SELECT @@DATEFIRST;  --1
GO
SET LANGUAGE us_english;
GO
SELECT @@DATEFIRST;  --7

You can use DATEFIRST to change that, the command below will make the week start at 1

T-SQL
1
SET DATEFIRST 1;
SET DATEFIRST 1;

So DATEPART(dw, Date) will return 7 for Sunday and 1 for Saturday in the US, in order to use this we need to add it like this in SQL… DATEPART(dw,SomeDate) IN(1,7). If you are not living where the week starts on a Sunday then modify the IN(1,7) part

Finally the query looks like this if you are not on SQL Server 2008 yet

T-SQL
1
2
3
SELECT * FROM TestData
WHERE SomeDate >=DATEADD(DD, DATEDIFF(dd, 0, DATEADD(WK,-4,GETDATE()))+0, 0)
AND DATEPART(dw,SomeDate  ) IN(1,7)
SELECT * FROM TestData
WHERE SomeDate >=DATEADD(DD, DATEDIFF(dd, 0, DATEADD(WK,-4,GETDATE()))+0, 0)
AND DATEPART(dw,SomeDate  ) IN(1,7)

If you are on SQl Server 2008, then you can use this query

T-SQL
1
2
3
SELECT * FROM TestData
WHERE SomeDate >=CONVERT(DATE,DATEADD(WK,-4,GETDATE()))
AND DATEPART(dw,SomeDate  ) IN(1,7)
SELECT * FROM TestData
WHERE SomeDate >=CONVERT(DATE,DATEADD(WK,-4,GETDATE()))
AND DATEPART(dw,SomeDate  ) IN(1,7)

Below are the execution plans, as you can see both queries result in an index seek

Why the top query takes 60% and the bottom one 40% is a little strange to me, I know that they did some optimizations where CONVERT(DATE, Column) …. is now SARGAble but this should be executed only once

I will investigate that part further

*** Remember, if you have a SQL related question, try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum