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

It's common to actually see a date table in databases sometimes that you can select against -- something like the following:

CREATE TABLE tDates (
  dtDate datetime NOT NULL,
  iYear int NOT NULL,
  iMonth int NOT NULL,
  iDayOfMonth int NOT NULL,
  /* iWeekNumber, day of week, whatever else is useful to you */
);

SELECT dtDate, 0, 0, 0, 0, 0, 0, 0 FROM tDates WHERE iYear = 2010;

The extra fields make it easy to query different date blocks. Generating the data should be trivial in C#/VB -- I'm sure some PowerShell gurus could whip up a script pretty easily as well. Just a small amount of time to prep a table like this can save you a lot of query pain in the long run.

Answer 2

This will give you a table with the numbers 1-370. After you've got that, JOIN on it with your START DATE and DATEADD the integer (0-370) until you hit the last date of the year.

SELECT  DATEADD(DD, X.Number, '12/31/2006'), 0, 0, 0, 0, 0, 0, 0, 0, 0
FROM (
SELECT TOP 370 ROW_NUMBER() OVER(ORDER BY Column_NAME) AS Number
FROM INFORMATION_SCHEMA.COLUMNS ) X
WHERE DATEADD(DD, X.Number, '12/31/2006') <= '12/31/2007'

Answer 3

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 4

Try this:

Declare @dt SmallDateTime Set @dt = '1 Jan 2007'
Declare @dts Table ( dt SmallDateTime Primary Key Not Null )
While (@dt < '1 Jan 2008') Begin
  Insert @dts(dt) Values(@dt)
  Set @dt = DateAdd(day, 1, @dt)
  End

Select dt, 0 intColA,
   0 intColB, 0 intColC, 0 inColD,
   0 intColE, 0 intColF, 0 inColG,
   0 intColH, 0 intColI, 0 inColJ
From @dts

Answer 5

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!

Answer 6

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 7

You should take a look at this website, it explains how to create a Date (or calendar) table in the database.

Here's some modified code sample from the website that should do the trick for you:

WITH CTE_DatesTable
AS
(
  SELECT CAST('20100101' as datetime) AS [date], 0 as one, 0 as two
  UNION ALL
  SELECT DATEADD(dd, 1, [date]), 0 as one, 0 as two
  FROM CTE_DatesTable
  WHERE DATEADD(dd, 1, [date]) <= '20101231'
)
SELECT * FROM CTE_DatesTable
OPTION (MAXRECURSION 0);

Of course you have to add all 9 "0" (I added two for example).