Click here to Skip to main content
Click here to Skip to main content

Get a table with all dates in a month

, 7 Mar 2008
Rate this:
Please Sign up or sign in to vote.
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

License

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

About the Author

TDunk
Chief Technology Officer TimeMap ApS
Denmark Denmark
No Biography provided

Comments and Discussions

 
GeneralThanks a lot PinmemberCum7me15-Dec-13 23:45 
QuestionCan also use CTE in SQL 2005 PinmemberGhepper2-Apr-08 13:31 
GeneralIt is possible to do this without using a table variable PinmemberNoam Helfman10-Mar-08 10:07 
local table variable and loops could slow down performance in high throughput scenarios.
You could use a common table expression to optimize this like the follow T-SQL code:

DECLARE @month datetime;
SET @month = dateadd(dd, -day(@date) + 1, @date); -- 1st day of the month
SET @month = dateadd(hh, -datepart(hh, @month), @month);
SET @month = dateadd(mi, -datepart(mi, @month), @month);
SET @month = dateadd(ss, -datepart(ss, @month), @month);
SET @month = dateadd(ms, -datepart(ms, @month), @month);
 
WITH MonthDays (DayInMonth) AS
(
SELECT 0 UNION ALL
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
....
....
SELECT 27 UNION ALL
SELECT 28 UNION ALL
SELECT 29 UNION ALL
SELECT 30 UNION ALL
SELECT 31
)
SELECT dateadd (dd, DayInMonth, @month) as [DateInMonth]
FROM MonthDays
WHERE month(dateadd (dd, DayInMonth, @month)) = month(@month)

 
This code could even be optimized further if needed.
 
My 2 cents. Smile | :)
GeneralWeak PinmemberMark Nischalke7-Mar-08 4:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 7 Mar 2008
Article Copyright 2008 by TDunk
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid