Get No of Sundays in a month SQL Server

The following stored procedure returns number of sundays in given month. It takes date as parameter. Month and year is extract from the date.

CREATE FUNCTION GetSundays (@giveDate DATETIME)
RETURNS INT
AS
BEGIN
	DECLARE @firstday DATETIME, @lastday DATETIME

	SELECT @firstday = DATEADD(month, DATEDIFF(month, 0, @giveDate), 0)
	SELECT @lastday = DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @giveDate) + 1, 0))

	DECLARE @count INT
	SET @count = 0

	WHILE (@firstday <= @lastday)
	BEGIN
		IF (datename(weekday, @firstday) = 'Sunday')
			SET @count = @count + 1
		SET @firstday = dateadd(day, 1, @firstday)
	END

	RETURN @count
END

Test Run

select dbo.GetSundays('2015-2-1')

return
4

Advertisements
This entry was posted in SQL Server 2008 and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s