SQL 每个月的第一天

Question

假设我想在 SQL 中编写表值函数，该函数返回一个表，其中参数日期之间包含每月的第一天，那么最简单的方法是什么？

例如 fnFirstOfMonths('10/31/10', '2/17/11') 将返回一个单列表，其中 11/1/10, 12/1/10、1/1/11 和 2/1/11 作为元素。

我的第一 react 是使用 while 循环并重复插入月份的第一天，直到到达开始日期之前。不过，似乎应该有一种更优雅的方法来做到这一点。

感谢您提供的任何帮助。

Answer 1

这样的事情无需在函数内部即可工作:

DECLARE @LowerDate DATE 
SET @LowerDate = GETDATE()

DECLARE @UpperLimit DATE
SET @UpperLimit = '20111231'

;WITH Firsts AS
(
    SELECT
        DATEADD(DAY, -1 * DAY(@LowerDate) + 1, @LowerDate) AS 'FirstOfMonth'

    UNION ALL

    SELECT
        DATEADD(MONTH, 1, f.FirstOfMonth) AS 'FirstOfMonth'
    FROM
        Firsts f
    WHERE
        DATEADD(MONTH, 1, f.FirstOfMonth)  <= @UpperLimit
)   
SELECT * 
FROM Firsts

它使用一种称为 CTE(通用表表达式)的东西 - 在 SQL Server 2005 及更高版本和其他数据库系统中可用。

在本例中，我通过确定指定的 @LowerDate 日期的第一个月来启动递归 CTE，然后迭代将一个月添加到前一个月份的第一天，直到上限已达到限制。

或者，如果您想将其打包在存储函数中，也可以这样做:

CREATE FUNCTION dbo.GetFirstOfMonth(@LowerLimit DATE, @UpperLimit DATE)
RETURNS TABLE 
AS 
   RETURN
      WITH Firsts AS
      (
          SELECT
             DATEADD(DAY, -1 * DAY(@LowerLimit) + 1, @LowerLimit) AS 'FirstOfMonth'
          UNION ALL
          SELECT
             DATEADD(MONTH, 1, f.FirstOfMonth) AS 'FirstOfMonth'
          FROM
             Firsts f
          WHERE
             DATEADD(MONTH, 1, f.FirstOfMonth)  <= @UpperLimit
       )    
       SELECT * FROM Firsts

然后这样调用它:

SELECT * FROM dbo.GetFirstOfMonth('20100522', '20100831')

得到这样的输出:

FirstOfMonth
2010-05-01
2010-06-01
2010-07-01
2010-08-01

PS:通过使用 SQL Server 2008 及更高版本中存在的 DATE 数据类型，我修复了 Richard 评论的两个“错误”。如果您使用的是 SQL Server 2005，则必须改用 DATETIME - 并处理您也获得时间部分的事实。