SQL Puzzle: SQL Advance Query – To find total number of Sundays in a given Year

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this puzzle, You have only two dates value, start-date and end-date. Find a total number of Sundays between given dates.

Output: Total 53 Sundays in year 2017

NumOfSunday

-----------

Solution:

declare @startdate datetime

declare @enddate datetime

set @startdate = '2017-01-01'

set @EndDate = '2017-12-31'

;with datecte as

(

select @startdate DateValue

union all

select DateValue + 1 from datecte

where DateValue + 1 <= @enddate

)

SELECT COUNT(1) as NumOfSunday FROM datecte

WHERE DATENAME(weekday,dateValue)='Sunday'

OPTION (maxrecursion 366)

Jun 20, 2017Anvesh Patel

Database Engineer