How to split date ranges based on months in SQL Se

问题:

Ex: date range is 01-01-2011 to 01-01-2012, I want the output like this :

 01-01-2011 31-01-2011
 01-02-2011 28-02-2011

How can I do this ? I'm using SQL Server 2005..

Thanks

回答1:

Using CTE:

DECLARE @Begin DATETIME
DECLARE @End DATETIME

SELECT @Begin = '20110101', @End = '20120101'

;WITH ranges(DateFrom, DateTo) AS
(
  SELECT @Begin, DATEADD(DAY, -1, DATEADD(MONTH, 1, @begin))
  UNION ALL
  SELECT DATEADD(month, 1, DateFrom), DATEADD(DAY, -1, DATEADD(MONTH, 2, DateFrom))
  FROM ranges
  WHERE DateFrom < @End
)
SELECT * FROM ranges
OPTION(MAXRECURSION 0)

And not using CTE:

DECLARE @Begin DATETIME
DECLARE @End DATETIME

SELECT @Begin = '20110101', @End = '20120101'

SELECT DATEADD(MONTH, n.Number, @Begin) DateFrom, DATEADD(day, -1, DATEADD(MONTH, n.Number+1, @Begin)) DateTo
FROM master.dbo.spt_values n
WHERE 
    n.Number < DATEDIFF(MONTH, @begin, @end)
AND n.Type = 'P'

If you need to include January 2012 too, use this

DECLARE @Begin DATETIME
DECLARE @End DATETIME

SELECT @Begin = '20110101', @End = '20120101'

SELECT DATEADD(MONTH, n.Number, @Begin) DateFrom, DATEADD(day, -1, DATEADD(MONTH, n.Number+1, @Begin)) DateTo
FROM master.dbo.spt_values n
WHERE 
    n.Number <= DATEDIFF(MONTH, @begin, @end)
AND n.Type = 'P'

And CTE:

DECLARE @Begin DATETIME
DECLARE @End DATETIME

SELECT @Begin = '20110101', @End = '20120101'

;WITH ranges(DateFrom, DateTo) AS
(
  SELECT @Begin, DATEADD(DAY, -1, DATEADD(MONTH, 1, @begin))
  UNION ALL
  SELECT DATEADD(month, 1, DateFrom), DATEADD(DAY, -1, DATEADD(MONTH, 2, DateFrom))
  FROM ranges
  WHERE DATEADD(month, 1, DateFrom) < @End
)
SELECT * FROM ranges
OPTION(MAXRECURSION 0)

回答2:

>On Providing the Start and End Date range, it can be split into months. This might help.
DECLARE @StartDate   datetime 
DECLARE @EndDate     datetime
DECLARE @tmpStart    datetime
DECLARE @tmpEnd      datetime

Select @StartDate   = '2012-01-01'
Select @EndDate     = '2009-01-01'

Select @StartDate = DATEADD(MONTH,1,Convert(DATETIME,Convert(VARCHAR(25), Datepart (YEAR,@StartDate)) + '-' +  Convert(VARCHAR(25), Datepart(MONTH, @StartDate)) + '-' + Convert(VARCHAR(25), 1))) 

Select @EndDate = DATEADD(MONTH,1,Convert(DATETIME,Convert(VARCHAR(25), Datepart(YEAR, @EndDate)) + '-' + Convert(VARCHAR(25), Datepart(MONTH, @EndDate)) + '-' + Convert(VARCHAR(25), 1)))

Select @EndDate = DATEADD(DD,-1, DATEADD(MM,1,@EndDate))

Select @tmpEnd = @EndDate
Select @tmpStart = Convert(DATETIME,Convert(VARCHAR(25), Datepart(YEAR, @tmpEnd)) + '-' + 
                  Convert(VARCHAR(25), Datepart(MONTH, @tmpEnd)) + '-' + Convert(VARCHAR(25), 1)) 

Select @StartDate 'Start' , @EndDate 'End' 

While @tmpStart <= @StartDate 
BEGIN   
    Select @tmpStart 'tmpStart' , @tmpEnd 'tmpEnd'  
    Select @tmpStart = DATEADD(MM,1,@tmpStart)
    Select @tmpEnd   = DATEADD(DD,-1,DATEADD (MM, 1 , @tmpStart)) 
END