## 10 replies

Last post Sep 13, 2010 06:33 AM by suresh dasari

None

0 Points

5 Posts

### find number of sundays between two dates using sql

can i find total number of sundays between two dates using sql? or total number of days between two days except sundays

SQL TIME AND DATE

Star

8807 Points

3271 Posts

### Re: find number of sundays between two dates using sql

Jun 25, 2009 09:53 AM|suthish nair|LINK

Member

540 Points

147 Posts

### Re: find number of sundays between two dates using sql

Declare @startdate datetime
Declare @enddate datetime
Declare @count int

set @count=0
set @startdate='2009-06-01'
set @enddate='2009-06-20'

while @startdate<=@enddate
Begin
IF DatePart(WEEKDAY,@startdate) <> 1
SET @count=@count+1
END
select @count

Contributor

2460 Points

751 Posts

### Re: find number of sundays between two dates using sql

```Create Function NumberOfSundays(@dFrom datetime, @dTo   datetime)
Returns int as
BEGIN
Declare @nSundays int
Set @nSundays = 0
While @dFrom <= @dTo Begin
If datepart(dw, @dFrom) = 1 Set @nSundays = @nSundays + 1
Set @dFrom = DateAdd(d, 1, @dFrom)
End
Return (@nSundays)
END
GO

-- Number of Sundays
Print dbo.NumberOfSundays()

-- Number of Other days
Print DateDiff(d, '2009/06/01', '2009/06/30') + 1 - dbo.NumberOfSundays('2009/06/01', '2009/06/30')```

Smile...tomorrow will be worse

Member

116 Points

56 Posts

### Re: find number of sundays between two dates using sql

Hi,

You can use this

Select

DateDiff(ww, '1/1/2009', '1/31/2009') as NumOfSundays

Regards,
Prathamesh.

Contributor

2460 Points

751 Posts

### Re: find number of sundays between two dates using sql

DateDiff(ww, '6/14/2009', '6/27/2009') returns 1 and should be 2, it seems first date is not inclusive.

In that case you can use  DateDiff(ww, @dt1-1, @dt2)  as a number of sundays

I didn't know ww works in that way (never used it before). I expected it would return number of whole weeks but in fact it really returns number of sundays (except if first date is sunday).

Smile...tomorrow will be worse

Participant

1640 Points

393 Posts

### Re: find number of sundays between two dates using sql

#### vineethavs@systemsvalley.co.in

can i find total number of sundays between two dates using sql? or total number of days between two days except sundays

For number of sundays...

```declare @startdate datetime, @enddate datetime
set @startdate = '01/01/2009'
set @enddate = '01/01/2010'
declare @numberOfSundays int
set @numberOfSundays = 0

set @enddate = dateadd(d, -1, @enddate)

while @startdate <= @enddate
begin
if datename(weekday, @startdate) = 'Sunday'
begin
set @numberOfSundays = @numberOfSundays + 1
end
set @startdate = dateadd(d, 1, @startdate)
end

select @numberOfSundays```

For number of days other than sundays..

```declare @startdate1 datetime, @enddate1 datetime
set @startdate1 = '01/01/2009'
set @enddate1 = '01/01/2010'
declare @numberOfSundays1 int
set @numberOfSundays1 = 0

set @enddate1 = dateadd(d, -1, @enddate1)

while @startdate1 <= @enddate1
begin
if datename(weekday, @startdate1) != 'Sunday'
begin
set @numberOfSundays1 = @numberOfSundays1 + 1
end
set @startdate1 = dateadd(d, 1, @startdate1)
end

select @numberOfSundays1```

Regards,
Yoga

Regards,
Yoga

All-Star

22512 Points

4552 Posts

### Re: find number of sundays between two dates using sql

declare @startdate datetime
declare @enddate datetime
declare @TotalSundays int
declare @TotalOtherDays int
set @startdate = '1/4/2009'
set @EndDate = '6/28/2009'

set @TotalSundays = (select  datediff(day,@startdate,@enddate) /7 +
case
when   datepart(Weekday,@StartDate) = 1 then 1
when datepart(Weekday,@StartDate) > datepart(Weekday,@EndDate) then 1
else 0
end)
set @TotalOtherDays = datediff(day,@StartDate,@EndDate) - @TotalSundays
select @TotalSundays
select @TotalOtherDays

Give a man a fish and you feed him for a day. Teach a man to fish and you feed him forever.
• ### limno

All-Star

119378 Points

9004 Posts

Moderator

### Re: find number of sundays between two dates using sql

Here is a sample I used CTE to generate a datetime table on the fly:

```set @startdate = '1/4/2009'
set @EndDate = '6/28/2009'

;with mycte as
(
select  @startdate DateValue
union all
select DateValue + 1 from mycte
where DateValue + 1 <= @enddate
)

SELECT COUNT(*) as NumOfSunday FROM mycte
WHERE DATENAME(weekday,dateValue)='Sunday'

OPTION (maxrecursion 366)```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm

All-Star

22069 Points

7804 Posts

### Re: find number of sundays between two dates using sql

Very nice!

Beware of bugs in the above code; I have only proved it correct, not tried it.
(Donald Knuth)

Visit my blog

Microsoft Community Contributor 2011-12

Contributor

2211 Points

708 Posts

### Re: find number of sundays between two dates using sql

Sep 13, 2010 06:33 AM|suresh dasari|LINK

My Blog