sql server - SQL first of every month -

supposing wanted write table valued function in sql returns table first day of every month between argument dates, simplest way this?

for example fnfirstofmonths('10/31/10', '2/17/11') return one-column table 11/1/10, 12/1/10, 1/1/11, , 2/1/11 elements.

my first instinct use while loop , repeatedly insert first days of months until before start date. seems there should more elegant way though.

thanks can provide.

something work without being inside function:

declare @lowerdate date  set @lowerdate = getdate()  declare @upperlimit date set @upperlimit = '20111231'  ;with firsts (     select         dateadd(day, -1 * day(@lowerdate) + 1, @lowerdate) 'firstofmonth'      union      select         dateadd(month, 1, f.firstofmonth) 'firstofmonth'             firsts f             dateadd(month, 1, f.firstofmonth)  <= @upperlimit )    select *  firsts 

it uses thing called cte (common table expression) - available in sql server 2005 , , other database systems.

in case, start recursive cte determining first of month @lowerdate date specified, , iterate adding 1 month previous first of month, until upper limit reached.

or if want package in stored function, can so, too:

create function dbo.getfirstofmonth(@lowerlimit date, @upperlimit date) returns table      return       firsts       (           select              dateadd(day, -1 * day(@lowerlimit) + 1, @lowerlimit) 'firstofmonth'           union           select              dateadd(month, 1, f.firstofmonth) 'firstofmonth'                        firsts f                        dateadd(month, 1, f.firstofmonth)  <= @upperlimit        )            select * firsts 

and call this:

select * dbo.getfirstofmonth('20100522', '20100831') 

to output this:

firstofmonth 2010-05-01 2010-06-01 2010-07-01 2010-08-01 

ps: using date datatype - present in sql server 2008 , newer - fixed 2 "bugs" richard commented about. if you're on sql server 2005, you'll have use datetime instead - , deal fact you're getting time portion, too.