Dates - Finding # Days In A Month

One of the most common question that people ask for the date functions in SQL Server is "how do I find the number of days in a given month". There is no direct method in SQL Server for finding this out, but we can play around a bit with the date functions of SQL Server and generate the value. Here is a simple algorithm for finding the number of days in a month.
  • Find the current day of the month (let's call it x)
  • Subtract x from 1. This should give us a negative number (let's call it y). Go back y days in the current month.
  • This will give the 1st day in the month (let's call it z)
  • Add 1 month to z. This gives the first day of the next month. Subtract 1 from it and this gives us the last day of the current month.
  • And that's what we want!!

Here is the T-SQL that will accomplish the above algorithm.


SELECT DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(GETDATE()), GETDATE())) - 1)
 
In the above algorithm, we used the current date function (called GETDATE()) and perform the algorithm on this function. The important functions that you need to note in this T-SQL statement are:
  • DAY. This function returns the current day number for a given date.
  • DATEADD. This functions adds the specified number of a "date component" to a given date. In the example above, we use the abbreviation d to make the function add days.

Ok, let's use the function that we have learned above, to print out the number of days in each month for a given year. Here is the T-SQL block for the same.

DECLARE @counter INT
DECLARE @days INT
DECLARE @date DATETIME
DECLARE @yearTable TABLE (monthName VARCHAR(20), numDays INT)
BEGIN
	SET @counter = 1
	WHILE (@counter < 13)
	BEGIN
		SET @date = CONVERT(DATETIME, STR(@counter) + '/01/2003')
		SELECT @days = DAY(DATEADD (m, 1, DATEADD (d, 1 - DAY(@date), @date)) - 1) 

		INSERT INTO @yearTable VALUES (DATENAME (month, @date), @days)
		SET @counter = @counter + 1
	END
	SELECT * FROM @yearTable
END

The code for generating the table is very simple. We use a table variable to store the result and use a loop variable to iterate through each month. We then form a date variable of the form month/01/2003 and then apply our function on this variable. Finally we print out the contents of this table. Here is what the output of the above program will look like:

Home