Get a table with all dates in a month

Introduction

When doing different joins it's often usefull to have a table that contains all dates in a specific month.

This is af table-valued function for SQL Server 2005 that creates such a table.

Using the code

The function "getFullmonth" looks like this:

-- =============================================
-- Description: Returns a table containing all dates in the month of the specified date
-- =============================================
ALTER FUNCTION [dbo].[getFullmonth]
(
 @date1 datetime
)
RETURNS @dates TABLE 
(
 date datetime not null 
)
AS
BEGIN
 -- Set first day in month
 DECLARE @month int;
 
 SET @month = datepart(MM, @date1);
 SET @date1 = convert(datetime, convert(varchar,datepart(yy,@date1)) + '.' + convert(varchar,@month) + '.01 00:00:00');
 WHILE datepart(MM,@date1) = @month
 BEGIN
  INSERT INTO @dates VALUES (@date1);
  SET @date1 = dateadd(dd, 1, @date1);
 END 
 RETURN;
END                      

After creating the function it can be used like this for different queries:

SELECT a.date, b.holidayname 
FROM dbo.getFullmonth(getdate()) a 
LEFT OUTER JOIN holiday b on a.date = b.holidaydate
ORDER BY 1, 2

If the above table "holiday" holds all public holidays the result will show the full month with the name for the holidays in the current month.

Points of Interest

The same princip can be used for returning all day numbers in a week.

Happy coding!

History

Version 1.0