finding sundays in given month using SQL Server

By: Suresh Dasari Sep 12, 2010

Introduction:

Here I will explain how to finding Sundays in given month using SQL Server.

Description:

Till now I kept some articles which are related to asp.net and c# the following query is used to find the Sundays in the given month, I hope it’s very useful any query regarding it you can post as comments

***************************************

Create PROCEDURE GetSundaysbyyearandmonth --2010,8

--2010 is year and 8 is month

@year varchar(10),

@month varchar(10)

as

begin

declare @date varchar(20)

--@first day is used to find the first day name in a month

declare @firstday varchar(50)

declare @start int

set @date=@month+'/'+'01'+'/'+@year

--print @date

declare @totaldays varchar(50)

set @totaldays=day(dateadd(mm,datediff(mm,-1,@year),-1))

set @firstday = datename(dw,@date)

--print @firstday

--SELECT datepart(dd,(getdate()));

set @start=

case @firstday when 'Monday'then 7

when 'Tuesday' then 6

when 'Wednesday'then 5

when 'Thursday'then 4

when 'Friday'then 3

when 'Saturday'then 2

else 1

end

print('sunday in given month are')

while(@start<=@totaldays)

begin

print @start

set @start=@start+7

end

************************************************

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

Subscribe by RSS

Subscribe by Email

3 comments :

Anonymous said...: Please verify this procedure using 2011 as year and 4 as month.

It's giving wrong answer; December 28, 2011 at 1:59 AM
Nikhil said...: Try This

DECLARE @date datetime

SELECT @date = GETDATE()

SELECT [1st_sunday], DATENAME(weekday, [1st_sunday]),
[sunday] = DATEADD(DAY, n * 7, [1st_sunday])
FROM
(
SELECT [1st_sunday] = [1st_month] + 8 - DATEPART(weekday, [1st_month])
FROM
(
SELECT [1st_month] = DATEADD(MONTH, DATEDIFF(MONTH, 0, @date), 0)
) d
) d
CROSS JOIN
(
SELECT n = 0 UNION ALL
SELECT n = 1 UNION ALL
SELECT n = 2 UNION ALL
SELECT n = 3 UNION ALL
SELECT n = 4
) n
WHERE DATEDIFF(MONTH, @date, DATEADD(DAY, n * 7, [1st_sunday])) = 0; April 19, 2013 at 7:11 AM
Anonymous said...: Working Fine; April 8, 2014 at 3:45 AM

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

Subscribe to: Post Comments ( Atom )

ASP.NET,C#.NET,VB.NET,JQuery,JavaScript,Gridview