Geek Answers Handbook

SQL first of every month

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

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

My first instinct is to simply use the while loop and repeatedly insert the first days of the months until I get to the start date. There seems to be a more elegant way to do this.

Thanks for any help you can provide.

+6
sql sql-server
source share
5 answers

Something like this will work without being inside the 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 later and other database systems.

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

Or if you want to pack it into a stored function, you can also do this:

 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 this conclusion:

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

PS: using the DATE data type, which is present in SQL Server 2008 and newer, I fixed two "errors" that Richard commented on. If you are on SQL Server 2005, you will have to use DATETIME instead - and deal with the fact that you also get some of the time.

+5
source share
 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 the source for general-purpose sequence numbers in SQL Server
  • dateadd (m, dateiff (m ) is a method for developing the first day of a month for any date.
  • + v.number is used to increase it by one month each time
  • 0 and dateiff (m, @ d1, @ d2) this condition gives us all the number we need to create a first-of-month date for each month between @ d1 and @ d2, including both months.
  • and dateadd (m, dateiff (m, 0, @ d1) + v.number, 0) between @ d1 and @ d2 is the last filter to make sure the date is first-of-month between @ d1 and @ d2

Performance Comparison with marc_s Code

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
+2
source share

It will loop only between months (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
0
source share

I understand that this is not a function, but I'm still going to throw it in the mix.

 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. Today I will send it to SQL Server and make some speed comparisons. (Why? Because it's funny, that's why.)

0
source share

Just in case, someone is still reading this ... I cannot understand that any of the above functions are faster:

 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
0
source share

More articles:


All Articles