# Date calendar 1 year

• ### Question

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

Regards,

Jonel
Tuesday, November 24, 2009 1:15 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 Tuesday, November 24, 2009 2:41 PM
• Proposed as answer by Tuesday, November 24, 2009 5:49 PM
• Marked as answer by 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 Tuesday, November 24, 2009 2:41 PM
• Proposed as answer by Tuesday, November 24, 2009 5:49 PM
• Marked as answer by 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