Home 2017 October SQL Puzzle: SQL Advance Query – Find the date of all third Sunday for Year 2017

SQL Puzzle: SQL Advance Query – Find the date of all third Sunday for Year 2017

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

Check the below input data and expected output to find the date of Third Sunday of all the months.

Input Year:

Expected Output:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Puzzle: SQL Advance Query - Check number is an Integer or not an IntegerSQL Puzzle: SQL Advance Query - Find the average shared salary by each department
Comments: 2
  1. Dinesh.IS
    October 24, 2017 at 5:34 am

    Declare @StartDate DateTime
    Declare @EndDate DateTime;

    Set @StartDate=’2017/01/01′
    Set @EndDate=’2017/12/31′
    ;
    WITH CTE
    As
    (Select @StartDate As StartDate,DateName(DW,@StartDate) As Day_Name
    Union All
    Select DateAdd(Day,1,StartDate),DateName(DW,DateAdd(Day,1,StartDate)) As Day_Name
    From CTE Where StartDate<@EndDate)
    Select StartDate From (Select Row_Number()Over(Partition By Month(StartDate) Order By StartDate) As RSLNo,* From CTE Where Day_Name='SunDay') A
    Where RSLNo=3
    OPTION(MAXRECURSION 0)

    ReplyCancel
  2. Nitin patel
    August 25, 2018 at 11:54 am

    declare @startdate datetime =’01-01-2017′
    declare @enddate datetime =’12-31-2017′
    ;with cte as
    (
    select CAst(dateadd(day,case datename(WEEKDAY,@startdate) when ‘Sunday’ then 14 when ‘Monday’ then 20 when ‘Tuesday’ then 19
    when ‘wednesday’ then 18 when ‘thursday’ then 17 when ‘Friday’ then 16 else 15 end,@startdate) as datetime) dt1
    union all
    select cast(dateadd(day,case datename(WEEKDAY,( dateadd (day,-(DATEPART(dd,(eomonth(DATEADD (MONTH,1,dt1)))))+1,eomonth(DATEADD(MONTH,1,dt1))))) when ‘Sunday’ then 14 when ‘Monday’ then 20 when ‘Tuesday’ then 19
    when ‘wednesday’ then 18 when ‘thursday’ then 17 when ‘Friday’ then 16 else 15 end, ( dateadd (day,-(DATEPART(dd,(eomonth(DATEADD (MONTH,1,dt1)))))+1,eomonth(DATEADD(MONTH,1,dt1))))) as datetime)
    from cte where dt1< @enddate
    )

    select * from cte

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

October 23, 2017 2 Comments SQL Puzzle, , , , , , , , , ,