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/开发者_JAVA技巧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.

Thanks for any help you can provide.

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.

create function dbo.fnFirstOfMonths(@d1 datetime, @d2 datetime)
returns table as return
select dateadd(m,datediff(m,0,@d1)+v.number,0) as FirstDay
from master..spt_values v
where v.type='P' and v.number between 0 and datediff(m, @d1, @d2)
  and dateadd(m,datediff(m,0,@d1)+v.number,0) between @d1 and @d2
GO

Notes

• master..spt_values is a source for general purpose sequence numbers in SQL Server
• dateadd(m, datediff(m is a technique for working out the first day of month for any date
• +v.number is used to increase it by one month each time
• 0 and datediff(m, @d1, @d2) this condition gives us all the numbers we need to generate a first-of-month date for each month between @d1 and @d2, inclusive of both months
• and dateadd(m,datediff(m,0,@d1)+v.number,0) between @d1 and @d2 the final filter to verify that the first-of-month date generated is between @d1 and @d2

Summary

8220 ms (CTE)
4173 ms (master..spt_values)

Test

declare @t table (dt datetime)
declare @d datetime
declare @i int
set nocount on

set @d = GETDATE()
set @i = 0
while @i < 10000
begin
insert @t select * from dbo.getfirstofmonth('20090102', '20100506')
delete @t
set @i = @i + 1
end

print datediff(ms, @d, getdate())

set @d = GETDATE()
set @i = 0
while @i < 10000
begin
insert @t select * from dbo.fnfirstofmonths('20090102', '20100506')
delete @t
set @i = @i + 1
end
print datediff(ms, @d, getdate())

Performante

It will loop just between the months involved (4 times in the example):

set dateformat mdy;
declare @date1 smalldatetime,@date2 smalldatetime,@i int
set @date1= '10-31-2010'
set @date2= '02-17-2011'
set @i=1

while(@i<=DATEDIFF(mm,@date1,@date2))
begin
    select  convert(smalldatetime,CONVERT(varchar(6),DATEADD(mm,@i,@date1),112)+'01',112)
    set     @i=@i+1
end

I realize this isn't a function, but I'm going to throw this into the mix anyway.

select cal_date from calendar
where day_of_month = 1
and cal_date between '2011-01-01' and '2012-01-01'

This calendar table runs on a PostgreSQL server at work. I'll port it to SQL Server tonight, and run some speed comparisons. (Why? Because this stuff is fun, that's why.)

Just in case anybody is still reading this ... I cannot imaging that any of the aforementioned functions is faster than this:

declare @DatFirst date = '20101031', @DatLast date = '21110217';

declare @DatFirstOfFirstMonth date = dateadd(day,1-day(@DatFirst),@DatFirst);

select  DatFirstOfMonth = dateadd(month,n,@DatFirstOfFirstMonth)
from    (
        select  top (datediff(month,@DatFirstOfFirstMonth,@DatLast)+1)
                n=row_number() over (order by (select 1))-1
        from    (values (1),(1),(1),(1),(1),(1),(1),(1)) a (n)
        cross join (values (1),(1),(1),(1),(1),(1),(1),(1)) b (n)
        cross join (values (1),(1),(1),(1),(1),(1),(1),(1)) c (n)
        cross join (values (1),(1),(1),(1),(1),(1),(1),(1)) d (n)
        ) x