SQLUSA

Microsoft SQL Server 2008 Best Practices

How to count Sundays between two dates?

 

Execute the following script in Query Editor get a count of Sunday in the given date range. datepart(dw,[Date]) = 1 indicates Sunday by default:

use AdventureWorks2008;

 

declare @StartDate datetime, @EndDate datetime

set @StartDate = '2009-01-01'

set @EndDate = '2010-06-01'

 

select Sundays=count(*)

from (select top (datediff (day, @StartDate, @EndDate) +1)

[Date] = dateadd(day, ROW_NUMBER()

over(order by c1.name, c2.name), convert(char(10),@StartDate-1,110))

from sys.columns c1

cross join sys.columns c2) x

where  datepart(dw,[Date]) = 1;

 

SQLUSA - The Best SQL Server 2008 Training in the World
 
 
SQLUSA.com Home Page