Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2012
    Posts
    188

    Unanswered: Create A Table That Shows Month, Day & Year

    How could I create a table that shows the month, each day in that month and the year and give me the option to set a start date & and end date? This is sql server 2005. I have used this before, but it doesn't allow me to see the days in the month. Essentially I want my output to be
    Day Month Year
    01-01-2012 January 2012
    .......
    01-02-2015 January 2015
    .....
    03-01-2015 March 2015
    ....
    08-02-2020 August 2020



    Code:
    CREATE TABLE #yourTempTable([MonthName] VARCHAR(9), [Year] INT);
    
    WITH CTE(N) AS (SELECT 1 FROM (SELECT 1 UNION ALL SELECT 1)a(N)),
    CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
    CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
    TALLY(N) AS (SELECT 0 UNION ALL
                 SELECT TOP 11 ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
                 FROM CTE3),
    DATETALLY(N) AS (SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE())+N, 0)
                     FROM TALLY)
    INSERT INTO #yourTempTable
    SELECT DATENAME(month,N), YEAR(N)
    FROM DATETALLY;
    
    SELECT *
    FROM #yourTempTable;

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just doodled this together, but I'm pretty sure that it will work. Bonus points to anyone who can tell me the significance of the dates that I picked for the demo!
    Code:
    DECLARE @dBegin DATETIME = '1962-09-12'
    ,  @dEnd        DATETIME = '1969-07-20'
    
    ; WITH c1 AS (
       SELECT 2048 * z1.number + z2.number AS number
          FROM master.dbo.spt_values AS z1
    	  CROSS JOIN master.dbo.spt_values AS z2
    	  WHERE  'P' = z1.type
    	     AND z2.type = z1.type
    		 AND 2048 * z1.number + z2.number < DateDiff(day, @dBegin, @dEnd)
    ), c2 AS (
       SELECT DateAdd(day, c1.number, @dBegin) AS thisDate
          FROM c1
    ), c3 AS (
       SELECT c2.thisDate, Year(c2.thisDate) AS Y
    ,     Month(c2.thisdate) AS m, Day(c2.thisDate) AS d
          FROM c2
    )
    SELECT *
       FROM c3
       ORDER BY c3.thisDate
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2012
    Posts
    188
    May not be what you are after...BUT significant things about each of those dates is
    09-12-1962 --- JFK gave his "Moon Speech" on this date
    07-20-1969 --- Neil Armstrong stepped onto the moon making the "Moon Speech" a reality

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Very good on the dates I picked!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2012
    Posts
    188
    Quote Originally Posted by Pat Phelan View Post
    I just doodled this together, but I'm pretty sure that it will work. Bonus points to anyone who can tell me the significance of the dates that I picked for the demo!
    Code:
    DECLARE @dBegin DATETIME = '1962-09-12'
    ,  @dEnd        DATETIME = '1969-07-20'
    
    ; WITH c1 AS (
       SELECT 2048 * z1.number + z2.number AS number
          FROM master.dbo.spt_values AS z1
    	  CROSS JOIN master.dbo.spt_values AS z2
    	  WHERE  'P' = z1.type
    	     AND z2.type = z1.type
    		 AND 2048 * z1.number + z2.number < DateDiff(day, @dBegin, @dEnd)
    ), c2 AS (
       SELECT DateAdd(day, c1.number, @dBegin) AS thisDate
          FROM c1
    ), c3 AS (
       SELECT c2.thisDate, Year(c2.thisDate) AS Y
    ,     Month(c2.thisdate) AS m, Day(c2.thisDate) AS d
          FROM c2
    )
    SELECT *
       FROM c3
       ORDER BY c3.thisDate
    -PatP
    That syntax works, is it possible to tweak it slightly to add Quarters in the years also so it would read like such (not exactly like that) everything can stay the same if the quarters could be added in the spots listed below for each year that would be perfection
    Code:
    Q1
    January - 2015
    February- 2015
    March- 2015
    Q2
    April- 2015
    May- 2015
    June- 2015
    Q3
    July- 2015
    August- 2015
    September- 2015
    Q4
    October- 2015
    November- 2015
    December- 2015

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •