How to get start date and end date between two dates in sql server

Question

Hi everyone i am working on some kind of accounting system and encountered some difficulties. the question is already been answered by Abdul Rasheed in this site.

This is the code

declare @sDate datetime,
    @eDate datetime

select @sDate = '2013-02-21',
    @eDate = '2013-04-25';

with CTE_TEST
as (
    select @sDate SDATE,
        DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @sDate) + 1, 0)) EDATE

    union all

    select EDATE + 1,
        DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DATEADD(MONTH, 1, SDATE)) + 1, 0))
    from CTE_TEST C
    where DATEADD(MONTH, 1, SDATE) < DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @eDate) + 1, 0))
    )
select DATENAME(MONTH, SDATE) MNAME,
    SDATE, (case when EDATE > @eDate then @eDate else EDATE end) EDATE
from CTE_TEST

The code works Great and just like what i want. but i want to insert the result to a new table. How do i do that?

For what it is worth, a recursive CTE is a [really inefficient way to generate a set or sequence](https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1). Also, if you want a permanent table of sequential dates, you should probably just create a [calendar table](http://www.sqlservercentral.com/blogs/dwainsql/2014/03/30/calendar-tables-in-t-sql/) — GarethD, Feb 24 '17 at 09:28

score 2 · Accepted Answer · answered Feb 24 '17 at 09:30

CREATE TABLE T(MNAME VARCHAR(20),SDATE DATETIME,EDATE DATETIME)  

      IF OBJECT_ID ('tempdb..#T') IS NOT  NULL
        DROP TABLE #T

          declare @sDate datetime,
            @eDate datetime
            select  @sDate = '2013-02-21',
            @eDate = '2013-04-25'
            ;WITH CTE_TEST AS (
            SELECT @sDate SDATE,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@sDate)+1,0)) EDATE
            UNION ALL
            SELECT  EDATE+1,DATEADD(s,-1,DATEADD(mm,DATEDIFF(m,0,DATEADD(MONTH,1,SDATE))+1,0))
            FROM    CTE_TEST C WHERE DATEADD(MONTH,1,SDATE) <   DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@eDate)+1,0))
    )   
             SELECT DATENAME(MONTH,SDATE) MNAME,SDATE,(CASE WHEN EDATE > @eDate THEN  @eDate ELSE EDATE END) EDATE INTO #T FROM CTE_TEST 



INSERT INTO T(  MNAME,
                SDATE,
                EDATE)
Select MNAME,
SDATE,
EDATE from #T

@RedietZewdu if it works can you please rate it as answer – mohan111 Feb 24 '17 at 10:22 — mohan111, Feb 24 '17 at 10:22

How to get start date and end date between two dates in sql server

1 Answers1