Sometimes you need a list of months between two dates. Admittedly, this is another case where a calendar table would come in VERY handy. Unfortunately, I have not been able to build one yet.
In case you’re in a similar situation, here’s how you could go about doing this:
CREATE FUNCTION dbo.GetMonthList (
@StartDate DATETIME,
@EndDate DATETIME
)
RETURNS @months TABLE (
[month] DATETIME
)
WITH EXECUTE AS OWNER AS
BEGIN
/******************************************************************************
* Author: Jeremiah Peschka
* Create Date: 2008-11-20
* Description: Create a table of dates between @StartDate and @EndDate
*****************************************************************************/
DECLARE @MonthDiff INT;
DECLARE @counter INT;
DECLARE @tbl TABLE ([month] DATETIME);
SET @StartDate = '2008-01-01';
SET @EndDate = '2008-12-01';
SET @counter = 0;
SELECT @MonthDiff = DATEDIFF(mm, @StartDate, @EndDate);
WHILE @counter <= @MonthDiff
BEGIN
INSERT @months
SELECT (DATEADD(mm, @counter, @StartDate));
SET @counter = @counter + 1;
END
RETURN;
END
GO
Comments
Excellent Article.
I am not an expert in SQL and your function has proved very nice for me.
One more task I need to perform. In my table I have a effective_date column for a employee.
When any change is made for the employee the effective_date is changed.Now what I need to do is find the months between these two dates. Initially I don’t know the second date.
I think you can help me in this regard.Your help is very much appreciated.Thanks.
Great function! Really helpful. Thanks a lot Jeremiah.
have u tried working on this function.. do you have a query to execute this function
SELECT * FROM dbo.GetMonthList(’2010-01-01′, ’2010-12-31′) should do the trick quite nicely.
This is really a gr8 help.
Thanks a ton
Hi, I tried this, but it doesn’t seem to work when I query another table. I wanted to use this function to query between two dates i.e.Project Start Date AND Project Finish Date. These two columns already exist in the table, but when I use the UDF above it throws an exception that ‘dbo.GetMonthList’ cannot be found
Without seeing your query, it’s tough to say what’s going on.