Here is an idea for you
DECLARE @Date DATETIME
SET @Date = GETDATE()
WHILE @Date < '2013-01-01'
INSERT INTO CalDay
( DayDate, NextDay, DayNum, NameOfDay )
SELECT dateadd(day,datediff(day, 0,@Date),0)
, dateadd(day,datediff(day, 1,@Date),0)
, DATEPART(WEEKDAY, @Date)
, CASE DATEPART(weekday, @Date)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
ELSE 'Saturday' END;
SET @Date = DATEADD(day, 1, @Date);
SELECT * FROM CALDAY
2012-03-09 00:00:00.000 2012-03-08 00:00:00.000 6 Friday
2012-03-10 00:00:00.000 2012-03-09 00:00:00.000 7 Saturday
2012-03-11 00:00:00.000 2012-03-10 00:00:00.000 1 Sunday
2012-03-12 00:00:00.000 2012-03-11 00:00:00.000 2 Monday
Just modify the WHILE @Date < '2013-01-01'
to the year you need / desire to stop adding to the table.
Whoops NextDay should be titled priorday or previousday.
And of course edit out the fields that you do not need and adjust the T-SQL accordingly.
If everything seems to be going well, you have obviously overlooked something.
Please help us, help you -before posting a question please read Before posting a performance problem please read