Splitting long span of time into month sections in sqlserver [Resolved]

Posted by Amritha444 under Sql Server on 3/14/2013 | Points: 10 | Views : 4044 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi all

I have two dates from date and to date .for eg: 2/3/2013 to 10/12/2013 .i want to split this into corresponding monthly sections in sqlserver 2008
fromdate todate
2/3/2013 31/3/2012
1/4/2013 30/4/2013
1/5/2013 31/5/2013
...........................
1/12/2013 10/12/2013

how to do this
Thanks in Advance
Amrutha

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 3/15/2013 [Member] [MVP] Silver | Points: 50

0	Check It Out! DECLARE @START DATETIME, @END DATETIME SELECT @START ='2013-03-02', @END = '2013-12-10' ;WITH CTE AS ( SELECT @START [Start] UNION ALL SELECT [Start]+1 FROM CTE WHERE [Start] < @END ) SELECT CONVERT(VARCHAR(10),MIN([Start]),103) [Start],CONVERT(VARCHAR(10),MAX([Start]),103) [End] FROM CTE GROUP BY YEAR([Start]),MONTH([Start]) OPTION(MAXRECURSION 0) GO Result FromDate ToDate 02/03/2013 31/03/2013 01/04/2013 30/04/2013 01/05/2013 31/05/2013 01/06/2013 30/06/2013 01/07/2013 31/07/2013 01/08/2013 31/08/2013 01/09/2013 30/09/2013 01/10/2013 31/10/2013 01/11/2013 30/11/2013 01/12/2013 10/12/2013 Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Amritha444, if this helps please login to Mark As Answer. \| Alert Moderator

Check It Out!

DECLARE @START DATETIME, @END DATETIME

SELECT @START ='2013-03-02', @END = '2013-12-10'



;WITH CTE

AS

(

SELECT @START [Start]

UNION ALL

SELECT [Start]+1 FROM CTE WHERE [Start] < @END 

)



SELECT CONVERT(VARCHAR(10),MIN([Start]),103) [Start],CONVERT(VARCHAR(10),MAX([Start]),103) [End] FROM CTE 

GROUP BY YEAR([Start]),MONTH([Start]) OPTION(MAXRECURSION 0)

GO

Result

FromDate	ToDate

02/03/2013	31/03/2013

01/04/2013	30/04/2013

01/05/2013	31/05/2013

01/06/2013	30/06/2013

01/07/2013	31/07/2013

01/08/2013	31/08/2013

01/09/2013	30/09/2013

01/10/2013	31/10/2013

01/11/2013	30/11/2013

01/12/2013	10/12/2013

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/14/2013 [Member] [MVP] Bronze | Points: 25

0	hi try this code Select Month('2/3/2013') Mark as Answer if its helpful to you Kumaraspcode2009@gmail.com Amritha444, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Amritha444 on: 3/15/2013 [Member] Starter | Points: 25

0	Thanks for the response What im given is only an example.for a input fromdate and to date i want to split as i mentioned in my question. i have an input from date and to this have a difference of more than one month. for an input 1/1/2013 and 8/4/2013 i want output as fromdate to date 1/1/2013 31/1/2013 1/2/2013 28/2/2013 1/3/2013 31/2/2013 1/4/2013 8/4/2013 Amritha444, if this helps please login to Mark As Answer. \| Alert Moderator

Thanks for the response

What im given is only an example.for a input fromdate and to date i want to split as i mentioned in my question. i have an input from date and to this have a difference of more than one month.
for an input 1/1/2013 and 8/4/2013 i want output as
fromdate to date
1/1/2013 31/1/2013
1/2/2013 28/2/2013
1/3/2013 31/2/2013
1/4/2013 8/4/2013

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Somu475 on: 3/15/2013 [Member] Starter | Points: 25

0	Hi, You can do something like this... declare @StartDate datetime declare @EndDate datetime select @StartDate = '2011-03-01' , @EndDate = '2011-11-01' ;WITH cte AS ( SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate, CASE WHEN DATEPART(Day,@StartDate) = 1 THEN DATEADD(Day,-1,(DATEADD(Month,1,@StartDate))) ELSE DATEADD(Day,-1,(DATEADD(Month,1,DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0)))) END AS myDate2 UNION ALL SELECT DATEADD(Month,1,myDate), DATEADD(Month,1,myDate2) FROM cte WHERE DATEADD(Month,1,myDate) <= @EndDate ) SELECT myDate, myDate2 FROM cte OPTION (MAXRECURSION 0) Change the query according to your need. Mark as answer if it helps you. -Somu Amritha444, if this helps please login to Mark As Answer. \| Alert Moderator

Hi,

You can do something like this...

declare @StartDate datetime

declare @EndDate datetime

select @StartDate = '2011-03-01' ,  @EndDate = '2011-11-01'

;WITH cte AS (

SELECT CASE WHEN DATEPART(Day,@StartDate) = 1 THEN @StartDate 

            ELSE DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0) END AS myDate,

CASE WHEN DATEPART(Day,@StartDate) = 1 THEN DATEADD(Day,-1,(DATEADD(Month,1,@StartDate)))

            ELSE DATEADD(Day,-1,(DATEADD(Month,1,DATEADD(Month,DATEDIFF(Month,0,@StartDate)+1,0)))) END AS myDate2



UNION ALL

SELECT DATEADD(Month,1,myDate), DATEADD(Month,1,myDate2)



FROM cte

WHERE DATEADD(Month,1,myDate) <=  @EndDate

)

SELECT myDate, myDate2

FROM cte

OPTION (MAXRECURSION 0)

Change the query according to your need.

Mark as answer if it helps you.

-Somu

Amritha444, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts