locked
Date calendar 1 year RRS feed

  • Question

  • Expert,
    I need date calendar a year by using SQL, please..

    Regards,

    Jonel
    Tuesday, November 24, 2009 1:15 PM

Answers

  • Give a look at the CAST AND CONVERT article in books online for formatting the dates.  I will be back with this particular format in a minute or a few.

    declare @year integer    set @year = 2010

    select
      right('0' + cast(day(calendar_date) as varchar(2)), 2) + '-'
        + left(datename(mm, calendar_date), 3 ) + '-'
        + cast(year(calendar_Date) as char(4))
      as Calendar_Date
    from small_numbers
    cross apply
    ( select dateadd(day, n - 1, dateadd(year, @year - 1900, 0)) as Calendar_Date ) x
    where n <= 366
      and year(Calendar_date) = @year

    /* -------- Sample Output: --------
    Calendar_Date
    -------------
    01-Jan-2010
    02-Jan-2010
    ...
    31-Dec-2010
    */

    EDIT:

    Using a Numbers CTE instead of a numbers table:

    declare @year integer    set @year = 2010

    ;WITH
    L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
    L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
    L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
    L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)

    select
      right('0' + cast(day(calendar_date) as varchar(2)), 2) + '-'
        + left(datename(mm, calendar_date), 3 ) + '-'
        + cast(year(calendar_Date) as char(4))
      as Calendar_Date
    from numbers
    cross apply
    ( select dateadd(day, n - 1, dateadd(year, @year - 1900, 0)) as Calendar_Date ) x
    where n <= 366
      and year(Calendar_date) = @year

    /* -------- Sample Output: --------
    Calendar_Date
    -------------
    01-Jan-2010
    02-Jan-2010
    ...
    31-Dec-2010
    */

    • Edited by Kent Waldrop Tuesday, November 24, 2009 2:41 PM
    • Proposed as answer by Abdshall Tuesday, November 24, 2009 5:49 PM
    • Marked as answer by Zongqing Li Monday, November 30, 2009 6:32 AM
    Tuesday, November 24, 2009 1:36 PM

All replies

  • Please describe what you want as input and what you want as output.  This can be done using either a calendar table or a table of numbers pluse a given base date.  Here is one example:

    declare @year integer    set @year = 2010

    select
    --  base_date,
      dateadd(day, n - 1, base_date) as calendar_Date
    from small_numbers
    cross apply ( select dateadd(year, 2010 - 1900, 0) as base_Date ) x
    where n <= 366
      and year(dateadd(day, n - 1, base_date)) = @year

    /* -------- Sample Output: --------
    calendar_Date
    -----------------------
    2010-01-01 00:00:00.000
    2010-01-02 00:00:00.000
    ...
    2010-12-31 00:00:00.000
    */

    Tuesday, November 24, 2009 1:23 PM
  • I need the date like below

    Date
    01-jan-09
    02-jan-09
    03-jan-09
    -
    -
    31-dec-09

    by using sql not from table.

    Tuesday, November 24, 2009 1:34 PM
  • Give a look at the CAST AND CONVERT article in books online for formatting the dates.  I will be back with this particular format in a minute or a few.

    declare @year integer    set @year = 2010

    select
      right('0' + cast(day(calendar_date) as varchar(2)), 2) + '-'
        + left(datename(mm, calendar_date), 3 ) + '-'
        + cast(year(calendar_Date) as char(4))
      as Calendar_Date
    from small_numbers
    cross apply
    ( select dateadd(day, n - 1, dateadd(year, @year - 1900, 0)) as Calendar_Date ) x
    where n <= 366
      and year(Calendar_date) = @year

    /* -------- Sample Output: --------
    Calendar_Date
    -------------
    01-Jan-2010
    02-Jan-2010
    ...
    31-Dec-2010
    */

    EDIT:

    Using a Numbers CTE instead of a numbers table:

    declare @year integer    set @year = 2010

    ;WITH
    L0 AS (SELECT 1 AS C UNION ALL SELECT 1), --2 rows
    L1 AS (SELECT 1 AS C FROM L0 AS A, L0 AS B),--4 rows
    L2 AS (SELECT 1 AS C FROM L1 AS A, L1 AS B),--16 rows
    L3 AS (SELECT 1 AS C FROM L2 AS A, L2 AS B),--256 rows
    L4 AS (SELECT 1 AS C FROM L3 AS A, L3 AS B),--65536 rows
    Numbers AS (SELECT ROW_NUMBER() OVER(ORDER BY C) AS N FROM L4)

    select
      right('0' + cast(day(calendar_date) as varchar(2)), 2) + '-'
        + left(datename(mm, calendar_date), 3 ) + '-'
        + cast(year(calendar_Date) as char(4))
      as Calendar_Date
    from numbers
    cross apply
    ( select dateadd(day, n - 1, dateadd(year, @year - 1900, 0)) as Calendar_Date ) x
    where n <= 366
      and year(Calendar_date) = @year

    /* -------- Sample Output: --------
    Calendar_Date
    -------------
    01-Jan-2010
    02-Jan-2010
    ...
    31-Dec-2010
    */

    • Edited by Kent Waldrop Tuesday, November 24, 2009 2:41 PM
    • Proposed as answer by Abdshall Tuesday, November 24, 2009 5:49 PM
    • Marked as answer by Zongqing Li Monday, November 30, 2009 6:32 AM
    Tuesday, November 24, 2009 1:36 PM
  • TRY

    USE tempdb
    GO
    
    CREATE FUNCTION dbo.fnCalendar(@Year INT)
    /*--------------------------------------------
    Ex.:
    
        SELECT * FROM dbo.fnCalendar(2008)
    
    --------------------------------------------*/
    RETURNS @Calendar TABLE(dtDate CHAR(15))
    BEGIN
        DECLARE @DT SMALLDATETIME
        SET @DT = CAST(@Year AS CHAR(4))+'-01-01'
        
        ;WITH CTE (dtDate) AS 
        (
            SELECT @DT 
            UNION ALL
            SELECT DATEADD(DAY, 1,dtDate) FROM cte WHERE dtDate < DATEADD(YEAR, DATEDIFF(YEAR, 30, @DT), 364)
        )
    
        INSERT INTO @Calendar (dtDate)
        SELECT REPLACE(CONVERT(VARCHAR(11),dtDate,106),' ',' - ') FROM CTE OPTION(MAXRECURSION 0)
        RETURN
    END
    
    
    GO
    
    SELECT * FROM dbo.fnCalendar(2009)
    

    Se a minha ajuda lhe for útil não esqueça de classificar. Att. Leonardo Marcelino
    Tuesday, November 24, 2009 2:18 PM
  • Perfect, thanks Kent

    Jonel
    Tuesday, November 24, 2009 4:33 PM
  • I'm glad it works for you, Jonel; and welcome to the Transact SQL forum.

    :-)
    Tuesday, November 24, 2009 5:14 PM