sqlsql-serverdate

How to get list of 2nd and 4th Saturday dates in SQL Server?


I am almost a newbie to writing SQL queries. In the context of SQL Server, how to get list of 2nd and 4th Saturday dates in the year 2016?


Solution

  • Done as a derived table simply to show the logic but you can reduce if you prefer:

    select *
    from (
        select d2016, 
               datename( weekday, d2016 ) as wkdy,
               row_number( ) over ( partition by datepart( month, d2016 ), datename( weekday, d2016 ) order by d2016 ) as rn_dy_mth
        from (
            select dateadd( day, rn, cast( '2016-01-01' as date ) ) as d2016
            from (
                select row_number() over( order by object_id ) - 1 as rn
                from sys.columns
                ) as rn
            ) as dy
        ) as dy_mth
    where rn_dy_mth in ( 2, 4 )
      and wkdy = 'Saturday' 
    order by d2016