Creating Table, Populating the Dates, Retrieving the Weekends in SQL Server 2005

Below Script Create the DateLookup Table:

CREATE TABLE DateLookup
(
DateKey INT PRIMARY KEY,
DateFull DATETIME,
CharacterDate VARCHAR(10),
FullYear CHAR(4),
QuarterNumber TINYINT,
WeekNumber TINYINT,
WeekDayName VARCHAR(10),
MonthDay TINYINT,
MonthName VARCHAR(12),
YearDay SMALLINT,
DateDefinition VARCHAR(30),
WeekDay TINYINT,
MonthNumber TINYINT
)

The below Script Populate the Dates in to the lookup table:

DECLARE @Date DATETIME
SET @Date = '1/1/2009'

WHILE @Date < '1/1/2011'
BEGIN
INSERT INTO DateLookup
(
DateKey, DateFull, FullYear,
QuarterNumber, WeekNumber, WeekDayName,
MonthDay, MonthName, YearDay,
DateDefinition,
CharacterDate,
WeekDay,
MonthNumber
)
SELECT
CONVERT(VARCHAR(8), @Date, 112), @Date, YEAR(@Date),
DATEPART(qq, @Date), DATEPART(ww, @Date), DATENAME(dw, @Date),
DATEPART(dd, @Date), DATENAME(mm, @Date), DATEPART(dy,@Date),
DATENAME(mm, @Date) + ' \' + CAST(DATEPART(dd, @Date) AS CHAR(2)) + ',\'
+ CAST(DATEPART(yy, @Date) AS CHAR(4)),
CONVERT(VARCHAR(10), @Date, 101),
DATEPART(dw, @Date),
DATEPART(mm, @Date)

SET @Date = DATEADD(dd, 1, @Date)
END


To Get the WeekEnds(Saturdays & Sundays) Use the Below Query:

select * from DateLookup where datepart(w,DateFull) in (1) or datepart(w,DateFull) in (7)