SQL Select all days in a year with 9 blank INT fields

Question

I am trying to get all dates in a year in a SELECT statement with 9 INT fields that have a value of 0 in them.

So basically I need the following:

1/1/2007 0 0 0 0 0 0 0 0 0
1/2/2007 0 0 0 0 0 0 0 0 0
1/3/2007 0 0 0 0 0 0 0 0 0 

and so on and so on for a whole year.

The year is always the 1/1 - 12/31

This is part of a bigger query but I don't think that part is necessary as this is the part I need....

Thanks!

Answer 1

Here you go:

WITH Dates AS (
        SELECT
         [Date] = CONVERT(DATETIME,'01/01/2010')
        UNION ALL SELECT
         [Date] = DATEADD(DAY, 1, [Date])
        FROM
         Dates
        WHERE
         Date < '12/31/2010'
) SELECT
 [Date],0,0,0,0,0,0,0,0,0
FROM
 Dates
 OPTION (MAXRECURSION 400)

Revel in the magic of recursive CTEs!

Answer 2

Altered to accommodate OP's desire to put the result into a @table.

DECLARE @year INT
   ,@startDate DATE
SET @year = 2010

SET @startDate = CAST(@year AS VARCHAR) + '-01-01'

DECLARE @DateTable TABLE
    (
     [Date] DATE ,[Col1] INT ,[Col2] INT ,[Col3] INT ,[Col4] INT
    ,[Col5] INT ,[Col6] INT ,[Col7] INT ,[Col8] INT ,[Col9] INT
    )
--
;
WITH    cte
          AS ( SELECT   @StartDate [Date]
               UNION ALL
               SELECT   DATEADD(DAY, 1, [Date])
               FROM     [cte]
               WHERE    [Date] < DATEADD(year, 1, @StartDate)
             )
    INSERT  INTO @DateTable
            ( 
             [Date] ,[Col1] ,[Col2] ,[Col3] ,[Col4] ,[Col5]
            ,[Col6] ,[Col7] ,[Col8] ,[Col9] )
            SELECT  [Date] ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0 ,0
            FROM    [cte] OPTION (MAXRECURSION 366)

---

If you're going to use this often,

consider creating a Date Table or at least a Tally Table

Answer 3

You could do this on the fly using a recursive CTE, assuming you are using SQL Server 2005 or later):

WITH CTEDates AS
(
SELECT CAST('20070101' AS DATETIME) AS DateVal
UNION ALL 
SELECT DATEADD(dd, 1, DateVal)
FROM CTEDates
WHERE DateVal < '20071231'
)

SELECT DateVal, 0,0,0,0,0,0,0,0,0
FROM CTEDates
OPTION (MAXRECURSION 366)

For more info on recursive CTEs, check out MSDN - it's a top well worth knowing about!