Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

finding sundays in given month using SQL Server

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
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 RSS subscribe by email Subscribe by Email

3 comments :

Anonymous said...

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

It's giving wrong answer

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

Anonymous said...

Working Fine

Give your Valuable Comments

Other Related Posts

© 2010-2012 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.