[SOLVED] SQL first of every month

SQL first of every month

Supposing that I wanted to write table valued function in SQL that returns a table with the first day of every month between the argument dates, what is the simplest way to do this?

For example, fnFirstOfMonths('10/31/10', '2/17/11') would return a one-column table with 11/1/10, 12/1/10, 1/1/11, and 2/1/11 as the elements.

My first instinct is just to use a while loop and repeatedly insert first days of months until I get to before the start date. It seems like there should be a more elegant way to do this though.

Solution

Something like this would work without being inside a function:

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

It uses a thing called CTE (Common Table Expression) - available in SQL Server 2005 and up and other database systems.

In this case, I start the recursive CTE by determining the first of the month for the @LowerDate date specified, and then I iterate adding one month to the previous first of month, until the upper limit is reached.

Or if you want to package it up in a stored function, you can do so, too:

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

and then call it like this:

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

to get an output like this:

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

PS: by using the DATE datatype - which is present in SQL Server 2008 and newer - I fixed the two "bugs" that Richard commented about. If you're on SQL Server 2005, you'll have to use DATETIME instead - and deal with the fact you're getting a time portion, too.