x

Split a parameterised date range into months - Urgents

How can I split a parameterised date range into months with start and end date for each month.

my stored procedure takes FromDate and ToDate parameters with certain range (Ex: '1-Mar-2011' to '31-Dec-2011') and in SP I want to divide this range into months with start and end date values which will be used in further query to get data between each start and end date.

So the date range should get split as:

 From - To
 ----------
 1-Mar-2011 - 31-Mar-2011
  ----------
 1-Apr-2011 - 30-Apr-2011
 ----------
 1-May-2011 - 31-May-2011
 ----------
 . 
 . 
 ---------
 1-Dec-2011 - 31-Dec-2011 

Please suggest how we can write the query.

more ▼

asked Jan 14, 2012 at 11:55 PM in Default

avatar image

Bins
68 4 5 9

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

If you would have two date params, you should first find the StartofMonth and EndOfMonth dates. I have used inline CTE for my script

 DECLARE @StartDate DATETIME, @EndDate DATETIME, @MonthDiff INT
 
 SET @StartDate = ''
 
 SET @EndDate = '9999-12-31'
 
 IF @EndDate > @StartDate --CHECK THE END DATE IS GREATER THAN START DATE
 BEGIN
     SET @MonthDiff = DATEDIFF(MONTH, @StartDate, @EndDate)
     --===== "Inline" CTE Driven "Tally Table" produces values from 0 to     
     -- more than 100,000... enough to cover this scenario  
     ;WITH E1(N) AS (
                      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 
                      UNION ALL                  
                      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                      SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                      ),                          --10E+1 or 10 rows       
           E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows       
           E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows 
           E5(N) AS (SELECT 1 FROM E4 a, E2 b), --MORE THAN ENOUGH TO HOLD MONTH DIFERENCE FOR DATETIME
           cteTally(N) AS ( --=== This provides the "zero base" and limits the number of rows right up front
                             -- for both a performance gain and prevention of accidental "overruns"                                          
                     SELECT 0 UNION ALL 
                     SELECT TOP (@MonthDiff) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E5
                     )
         SELECT [FIRST DAY OF THE MONTH] = DATEADD(MONTH, N, CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@StartDate)-1),@StartDate),101)),
                [SECOND DAY OF THE MONTH] = DATEADD(MONTH, N, CONVERT(VARCHAR(25),DATEADD(ms, -3, DATEADD(MONTH, 1, DATEADD(dd,-(DAY(@StartDate)-1),@StartDate))),101))
         FROM [cteTally]
 
 END
more ▼

answered Jan 16, 2012 at 01:38 AM

avatar image

Sacred Jewel
1.7k 3 7 10

+1

Maybe I only use

 SELECT 
     [FIRST DAY OF THE MONTH] = DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + N, 0),
     [SECOND DAY OF THE MONTH] = DATEADD(Day, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + N + 1, 0))
 FROM [cteTally]

To avoid var char convertion. Of Course in this case it will not work for the '9999/12/xx' as end dates as it will cause DateTime to overflow.

Jan 16, 2012 at 01:24 PM Pavel Pawlowski

@Pavel Pawlowski The conversion is there to truncate the time portion of the date params if supplied, which I thought was the requirement of the OP :)

Jan 18, 2012 at 05:57 AM Sacred Jewel

If working with date time, then there is no need to trucate the Time portion of the date time as in the of my sample it simply changes the time portion to 00:00:00.000.

Anyway as it is on SQL Server 2008 then I would use a Date data type which does not have any time portion. :-)

Jan 18, 2012 at 06:00 AM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left

You mean something like this?

 declare @Absstartdate date = '01-01-2011'
 declare @AbsendDate date = '01-01-2012'
 
 create table #DateRanges (TheDate varchar(50))
 
 while @Absstartdate < @AbsendDate begin
 
 insert into #DateRanges
 select CONVERT(varchar, @Absstartdate) + ' - ' + CONVERT(varchar, dateadd(d, -1, dateadd(m, 1, @AbsStartDate)))
 
 select @Absstartdate = DATEADD(M, 1, @Absstartdate)
 end
 
 select TheDate from #DateRanges
more ▼

answered Jan 15, 2012 at 12:35 PM

avatar image

SirSQL
4.9k 4 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2188
x439

asked: Jan 14, 2012 at 11:55 PM

Seen: 3149 times

Last Updated: Jan 16, 2012 at 05:45 AM

Copyright 2017 Redgate Software. Privacy Policy