Geek Answers Handbook

SQL Select all days of the year with 9 empty INT fields

I am trying to get all the dates of the year in an instruction SELECTwith 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

etc. etc. for a whole year.

The year is always 1/1 - 12/31

This is part of a big request, but I don’t think this part is necessary, since this is the part I need ....

Thank!

+5
source share
7 answers

Modified to accommodate the OP's desire for the result to be obtained in @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 are going to use this often,

consider creating Date Tableor at least aTally Table

+3
source

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)

Bring the magic of recursive CTE!

+7

, CTE, SQL Server 2005 ):

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)

CTE, MSDN - , !

+3

, - - :

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;

. #/VB - , PowerShell script. , , , .

+1

1-370. , , START DATEADD (0-370), .

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'
+1

:

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
0

, , ( ) .

, :

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);

, 9 "0" ( , , ).

0

More articles:


All Articles