How to find sundays in sql server.

Posted by Jayakumars under Regular Expressions on 6/24/2011 | Points: 10 | Views : 7085 | Status : [Member] [MVP] | Replies : 5
hi


1.I need How to find How many Sundays Particular month
2.Which day have sun days like this this month-> 5,12,19,26 Sunday
3.How many days particular month
how to use sql query

Mark as Answer if its helpful to you


Responses

Posted by: Siva.boggarapu on: 6/24/2011 [Member] Starter | Points: 25

Up
0
Down
Hello Rajkuar

Please find the answer

1.I need How to find How many Sundays Particular month

A) ValueCount(DayNameDates( "sunday" ; "01.03.2009" ; "31.03.2009" )) = 5


Regards,
siva,
skyme:siva.boggarapu.ef

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 6/24/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

How many days in a particular Month
Create Function Dbo.HowManyDays(@Month	Varchar(15)) Returns Int

As
Begin
Declare @Date Varchar(50),
@Days TinyInt

Select @Date = '2011-' + @Month + '-01',
@Days = DatePart(Day,DateAdd(Month,1,@Date) -1)

Return @Days
End
Select Dbo.HowManyDays('June') [Days]

Go
How many Sundays in particular Month
Declare @Days	TinyInt,

@Month Varchar(15)

Select @Month = 'May'
Select @Days = Dbo.HowManyDays(@Month)

;With Sundays
As
(
Select 1 [Days]
Union All
Select [Days] + 1 from Sundays where [Days] <31
)

Select SUM([Day]) [Sundays] from
(
select CASE DATENAME(weekday,'2011-' + @Month + '-' + Cast([Days] as varchar)) WHEN 'Sunday' Then 1 Else 0 End as [Day] from Sundays Where [Days] <= @Days
) as X
List of Sundays in Particulay Month
Declare @Days	TinyInt,

@Month Varchar(15)

Select @Month = 'June'
Select @Days = Dbo.HowManyDays(@Month)

;With Sundays
As
(
Select 1 [Days]
Union All
Select [Days] + 1 from Sundays where [Days] <31
)

Select [Days] [Sundays] from
(
select [Days] from Sundays Where [Days] <= @Days and DATENAME(weekday,'2011-' + @Month + '-' + Cast([Days] as varchar)) = 'Sunday'
) as X

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vilasgite080 on: 6/25/2011 [Member] Starter | Points: 25

Up
0
Down

just open link

http://stackoverflow.com/questions/2357973/find-the-friday-that-a-particular-date-is-closest-to-in-t-sql

If this reply hepls your post.then "MARK AS ANSWER"!

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vilasgite080 on: 6/25/2011 [Member] Starter | Points: 25

Up
0
Down


hi
please run this statements (for exmpl for all sundays in year 2011)

SET NOCOUNT ON
SET DATEFIRST 4

Declare @DateValue DateTime

SET @DateValue = '01/01/2011'

While @DateValue < '12/31/2011'
BEGIN
PRINT DateAdd (Day, 4 - DatePart (dw, @DateValue), @DateValue)
SET @DateValue = @DateValue + 1
END
---------------------------------------------
If this reply hepls your post.then "MARK AS ANSWER"!


Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vilasgite080 on: 6/26/2011 [Member] Starter | Points: 25

Up
0
Down
hi
are you got sundays?



Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response