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:
ALTER FUNCTION [dbo].[getFullmonth]
(
@date1 datetime
)
RETURNS @dates TABLE
(
date datetime not null
)
AS
BEGIN
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