# Split date range into day wise rows in sql

### Question

• Hello

IP:

DATE_FROM:   2008-02-25  DATE_TO: 2008-02-28

OP:

2008-02-25

2008-02-26

2008-02-27

2008-02-28

Wednesday, September 11, 2013 9:27 AM

• Try the below:

```Declare @s Date ='2008-02-25', @t Date='2008-02-28'

;with
N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
FROM nums
WHERE num <= DATEDIFF(day,@s,@t) + 1```

• Marked as answer by Wednesday, September 11, 2013 11:14 AM
Wednesday, September 11, 2013 9:41 AM
• Its working for small date range but for large range its giving error

"the statement terminated. The maximum recursion 100 has been exhausted before statement completion."

```DECLARE @sdate DATE='2008-02-25'
DECLARE @edate DATE='2009-02-28'

;WITH rs
AS
(
SELECT @sdate dt
UNION all
SELECT DATEADD(d,1,dt)    FROM rs WHERE dt<@edate
)
SELECT * FROM rs
option (maxrecursion 0)```
vt

• Marked as answer by Wednesday, September 11, 2013 12:32 PM
Wednesday, September 11, 2013 11:21 AM

### All replies

• Try the below:

```Declare @s Date ='2008-02-25', @t Date='2008-02-28'

;with
N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
FROM nums
WHERE num <= DATEDIFF(day,@s,@t) + 1```

• Marked as answer by Wednesday, September 11, 2013 11:14 AM
Wednesday, September 11, 2013 9:41 AM
• pls try

```DECLARE @sdate DATE='2008-02-25'
DECLARE @edate DATE='2008-02-28'

;WITH rs
AS
(
SELECT @sdate dt
UNION all
SELECT DATEADD(d,1,dt)    FROM rs WHERE dt<@edate
)
SELECT * FROM rs```

vt

Wednesday, September 11, 2013 9:51 AM
• Its working for small date range but for large range its giving error

"the statement terminated. The maximum recursion 100 has been exhausted before statement completion."

Wednesday, September 11, 2013 11:13 AM
• Thanks a lot, ultimate answer
Wednesday, September 11, 2013 11:14 AM
• Its working for small date range but for large range its giving error

"the statement terminated. The maximum recursion 100 has been exhausted before statement completion."

```DECLARE @sdate DATE='2008-02-25'
DECLARE @edate DATE='2009-02-28'

;WITH rs
AS
(
SELECT @sdate dt
UNION all
SELECT DATEADD(d,1,dt)    FROM rs WHERE dt<@edate
)
SELECT * FROM rs
option (maxrecursion 0)```
vt

• Marked as answer by Wednesday, September 11, 2013 12:32 PM
Wednesday, September 11, 2013 11:21 AM
• Its working for small date range but for large range its giving error

"the statement terminated. The maximum recursion 100 has been exhausted before statement completion."

You may try with CTE as below:

```DECLARE @sdate DATE='2008-02-25'
DECLARE @edate DATE='2013-02-28'

;WITH rs
AS
(
SELECT @sdate dt
UNION all
SELECT DATEADD(d,1,dt)    FROM rs WHERE dt<@edate
)
SELECT * FROM rs OPTION (MAXRECURSION 32767)```