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

Posted by Amritha444 under Sql Server on 3/14/2013 | Points: 10 | Views : 2951 | Status : [Member] | Replies : 4
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




Responses

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

Up
0
Down

Resolved
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

Up
0
Down
hi

try this code

Select Month('2/3/2013')

Mark as Answer if its helpful to you

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

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

Up
0
Down
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

Up
0
Down
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

Login to post response