Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Get a table with all dates in a month

1.33/5 (3 votes)
7 Mar 2008CPOL 1  
This SQL Server 2005 Table values function returns a table with all dates of a month for a provided date.

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:

SQL
-- =============================================
-- 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:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)