Get Days Of Month

This function returns a table which contains one raw per day of the month of a given year and month. This function makes a great addition to a reporting parameter procedure (think SSRS).

  • Example: SELECT * FROM dbo.DaysOfMonth(2013, 6)

Returns:

Day Date
1 2013-06-01 00:00:00.000
2 2013-06-02 00:00:00.000
3 2013-06-03 00:00:00.000
... (all the other days)
28 2013-06-28 00:00:00.000
29 2013-06-29 00:00:00.000
30 2013-06-30 00:00:00.000 
CREATE FUNCTION [dbo].[DaysOfMonth]
(
	@Year	INT,
	@Month	INT
)
RETURNS @Days TABLE 
(
	ID INT IDENTITY(1,1),
	Date DateTime
)
AS
BEGIN
	DECLARE @Date	DateTime

	SET @Date = Convert(VarChar(10), @Month) + '/1/' + Convert(VarChar(10), @Year)

	WHILE(MONTH(@Date) = @Month)
	BEGIN--WHILE
		INSERT INTO @Days(Date) SELECT @Date

		SET @Date = DATEADD(D, 1, @Date)
	END--WHILE

	RETURN 
END
Tags
snippet sql-server