# Usnig Recursive CTE to Create Dates

### Question

•  Hi,

I am wondering if anyone has some experience with using CTE recursive logic, to create what it takes me a bunch of code in the function below.

Thanks a lot for any feedback

CREATE FUNCTION [dbo].[ufn_YearMonths]

(

)

RETURNS

@gencalendar TABLE

(

[dt] SMALLDATETIME

)

AS

BEGIN

DECLARE @FirstYearDate AS SMALLDATETIME

SET @FirstYearDate = GETDATE() -1

;WITH Months (DT) AS

(

SELECT CONVERT(VARCHAR(10), DATEADD(yy, -1, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -11, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -10, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -9, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -8, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -7, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -6, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -5, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -4, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -3, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -2, @FirstYearDate), 101)AS DT

UNION

SELECT CONVERT(VARCHAR(10), DATEADD(mm, -1, @FirstYearDate), 101) AS DT

UNION

SELECT CONVERT(VARCHAR(10),GETDATE()-1, 101) AS DT

)

INSERT INTO @gencalendar

SELECT * FROM Months

ORDER BY dt

RETURN

END

Output:

2008-01-17 00:00:00
2008-02-17 00:00:00
2008-03-17 00:00:00
2008-04-17 00:00:00
2008-05-17 00:00:00
2008-06-17 00:00:00
2008-07-17 00:00:00
2008-08-17 00:00:00
2008-09-17 00:00:00
2008-10-17 00:00:00
2008-11-17 00:00:00
2007-12-17 00:00:00
2008-12-17 00:00:00

donnie100
Thursday, December 18, 2008 10:04 PM

### All replies

• May be having an auxiliary table of numbers. Here is an idea from Itzik Ben-Gan.

CREATE FUNCTION dbo.ufn_Numbers(@max INT)

RETURNS TABLE

AS

RETURN (

WITH

L0 AS (SELECT 1 AS c1 UNION ALL SELECT 1),

L1 AS (SELECT 1 AS c1 FROM L0 AS a, L0 AS b),

L2 AS (SELECT 1 AS c1 FROM L1 AS a, L1 AS b),

L3 AS (SELECT 1 AS c1 FROM L2 AS a, L2 AS b),

L4 AS (SELECT 1 AS c1 FROM L3 AS a, L3 AS b)

SELECT TOP (@max) ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS Number

FROM L4

)

GO

CREATE FUNCTION [dbo].[ufn_YearMonths](@FirstYearDate SMALLDATETIME)

RETURNS TABLE

AS

RETURN (

SELECT

UNION ALL

SELECT

DATEADD(month, -1 * (n.[Number] - 1), @FirstYearDate) AS DT

FROM

(

SELECT Number

FROM dbo.ufn_Numbers(11)

) AS n

)

GO

DECLARE @dt SMALLDATETIME

SET @dt = GETDATE() - 1

SELECT *

FROM dbo.[ufn_YearMonths](@dt)

ORDER BY dt

GO

DROP FUNCTION dbo.ufn_Numbers

GO

DROP FUNCTION dbo.[ufn_YearMonths]

GO

AMB

Thursday, December 18, 2008 10:40 PM
•
 CREATE FUNCTION ufn_YearMonths ( ) RETURNS @gencalendar TABLE ([dt] SMALLDATETIME) AS BEGIN DECLARE @FirstYearDate AS SMALLDATETIME SET @FirstYearDate = GETDATE() -1 ;WITH Months AS ( SELECT @FirstYearDate dt , -11 AS i UNION ALL SELECT DATEADD(mm, -1, dt),i + 1 FROM Months WHERE i <0 ) INSERT INTO @gencalendar SELECT CONVERT(VARCHAR(10),dt, 101) FROM Months ORDER BY dt RETURN END GO SELECT * FROM ufn_YearMonths() DROP FUNCTION  ufn_YearMonths
Friday, December 19, 2008 3:54 AM
• Thursday, August 02, 2012 4:49 AM
• Or a more generic way?

```CREATE FUNCTION dbo.ufn_YearMonths
(
@FromDate DATETIME,
@ToDate DATETIME
)
RETURNS TABLE
AS
RETURN	(
WITH	L0 AS (SELECT 1 AS c1 UNION ALL SELECT 1),
L1 AS (SELECT 1 AS c1 FROM L0 AS a, L0 AS b),
L2 AS (SELECT 1 AS c1 FROM L1 AS a, L1 AS b),
L3 AS (SELECT 1 AS c1 FROM L2 AS a, L2 AS b),
L4 AS (SELECT 1 AS c1 FROM L3 AS a, L3 AS b),
cteSource(Number)
AS (
SELECT	TOP (1 + DATEDIFF(MONTH, @FromDate, @ToDate))
ROW_NUMBER() OVER(ORDER BY (SELECT 0)) - 1 AS Number
FROM	L4
)
SELECT	DATEADD(MONTH, Number, '17530101') AS StartOfMonth,
FROM	cteSource
)
GO
SELECT	*
FROM	dbo.ufn_YearMonths('19530811', '20340407')
GO```

N 56°04'39.26"
E 12°55'05.63"

Thursday, August 02, 2012 5:45 AM
• This one too
```CREATE  FUNCTION [dbo].[ufn_YearMonths]
(
)
RETURNS
@gencalendar TABLE
(
[dt] SMALLDATETIME, Row Int, Dt1 DAteTime
)
AS
BEGIN
DECLARE @FirstYearDate AS SMALLDATETIME
SET @FirstYearDate = GETDATE() -1
;WITH Months (DT, Row, DT1) AS
(
SELECT  @FirstYearDate as DT,-12 as Row , @FirstYearDate  as DT1
UNION ALL
SELECT DAteAdd(Month,row,@FirstYearDate) DT, Row+1 as row, DT as Dt1 from months
where Row<0
)
INSERT INTO @gencalendar
SELECT DT , Row, DT1 FROM Months
ORDER BY dt
RETURN
END
```
Regards
Satheesh
Thursday, August 02, 2012 11:00 AM
• Hi,

If you are looking for a recursive CTE that will give list of dates as output then following code can help you.

Declare @StartDate smalldatetime='2008-01-17',@Enddate smalldatetime='2008-01-31'

;With CTE
As
(
Select @StartDate As dt
Union ALL
Select DATEADD(DD,1,dt) from CTE where dt<@Enddate
)

select CONVERT(varchar,dt,101)as dt from CTE

Aftab Ansari

Thursday, August 02, 2012 1:04 PM