Date calendar 1 year

Question

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

Regards,

Jonel

Answer 1

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
*/

Answer 2

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.

Answer 3

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
*/

Answer 4

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

Answer 5

Perfect, thanks Kent

Jonel

Answer 6

I'm glad it works for you, Jonel; and welcome to the Transact SQL forum.

:-)