Get a table with all dates in a month
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:
-- =============================================
-- 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