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

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

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

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

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

SELECT @@DATEFIRST

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

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

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

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

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