如何在 SQL Server 2005 中根据月份拆分日期范围 [英] How to split date ranges based on months in SQL Server 2005

问题描述

例如:日期范围是 01-01-2011 到 01-01-2012，我想要这样的输出:

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

我该怎么做?我使用的是 SQL Server 2005..

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

谢谢

推荐答案

使用 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)

并且不使用 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'

如果您还需要包括 2012 年 1 月，请使用此

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'

和 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)

这篇关于如何在 SQL Server 2005 中根据月份拆分日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！