SELECT TOP 366
IDENTITY(INT, 1, 1) AS n
DATEADD(DAY, n, '1/1/2008')
DATEPART(weekday, DATEADD(DAY, n, '1/1/2008')) =
WHEN 7 THEN 4
WHEN 6 THEN 5
WHEN 5 THEN 6
WHEN 4 THEN 7
WHEN 3 THEN 1
WHEN 2 THEN 2
DROP TABLE #nums
The first section just creates a temporary numbers table and you can do that several ways, I just opted for a simple one. If you don't have a numbers table already, you should as there are many good uses for it.
Then I just run a simple select against the temporary numbers table adding each number to the first day of the year. The WHERE clause then just compares the value returned from the DATEPART function (weekday) to the value for Wednesday based on the @@Datefirst value. According to Books on line @@Datefirst:
SET DATEFIRST indicates the specified first day of each week. The U.S. English default is 7, Sunday.
Language settings affect date information.
This can also be affected by using SET DATEFIRST if you want it to be different from the default for the language. This case statement is important if you expect the code to work on any database in any SQL Server install.
Ideally to solve a problem like this you would have a dates table as Jonathan recommends in the thread.
For more information on a numbers table check out these articles: