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?
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