Click here to Skip to main content
13,197,342 members (28,652 online)
Click here to Skip to main content
Add your own
alternative version


14 bookmarked
Posted 7 Mar 2008

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.


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
 date datetime not null 
 -- 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
  INSERT INTO @dates VALUES (@date1);
  SET @date1 = dateadd(dd, 1, @date1);

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

SELECT, b.holidayname 
FROM dbo.getFullmonth(getdate()) a 
LEFT OUTER JOIN holiday b on = b.holidaydate

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!


Version 1.0


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


About the Author

Chief Technology Officer TimeMap ApS
Denmark Denmark
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralThanks a lot Pin
Cum7me15-Dec-13 23:45
memberCum7me15-Dec-13 23:45 
QuestionCan also use CTE in SQL 2005 Pin
Ghepper2-Apr-08 13:31
memberGhepper2-Apr-08 13:31 
GeneralIt is possible to do this without using a table variable Pin
Noam Helfman10-Mar-08 10:07
memberNoam Helfman10-Mar-08 10:07 
GeneralWeak Pin
Mark Nischalke7-Mar-08 4:46
memberMark Nischalke7-Mar-08 4:46 
I would expect a more in-depth article, possibly outlining more techniques for comparison and explaining the benefits of this one, a little more explanation of what is happening. If the title Chief Technology Officer is real and deserved, which I doubt, it shouldn't be a challenge for you.

"The same princip can be used for returning all day numbers in a week." Show an example, put a little more effort into it.

only two letters away from being an asset

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 7 Mar 2008
Article Copyright 2008 by TDunk
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid