none
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

Answers

  • 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)
    SELECT DATEADD(day,num-1,@s) as thedate
    FROM nums
    WHERE num <= DATEDIFF(day,@s,@t) + 1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Rohitz 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


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by Rohitz 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)
    SELECT DATEADD(day,num-1,@s) as thedate
    FROM nums
    WHERE num <= DATEDIFF(day,@s,@t) + 1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by Rohitz 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


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    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


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Marked as answer by Rohitz 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)


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 11, 2013 11:22 AM