First and last Saturday of a month in SQL Server

These two are little tricky but commonly asked interview questions.

Q: Find the first and last Saturday of the current month?

Q: Find the first and last Saturday for all the months of current year?

Prasad Sahoo

Prasad Sahoo, is an enthusiast of Microsoft technologies in general and a passionate database professional, mainly focusing on SQL Server performance tuning and business intelligence. He has contributed a long time of his career working on SQL Server and other RDBMS. If he is not busy with SQL stuff then he must be watching some Hollywood movies.

2 thoughts on “First and last Saturday of a month in SQL Server

  • February 22, 2016 at 10:46 am
    Permalink

    — applies similar CTE in your another post find weekends between two dates:)

    DECLARE @fromD date = ‘2016-01-01’, @toD date = ‘2016-12-31’
    ;WITH cteDate as (
    SELECT ROW_NUMBER() OVER(ORDER BY (select null)) as id, @fromD as dt, DATENAME(dw, @fromD) as dtName
    UNION ALL
    SELECT id + 1 as id, DATEADD(day, 1, dt), DATENAME(dw, DATEADD(day, 1, dt)) dtName
    FROM cteDate
    WHERE dt < @toD
    )
    , cteSaturday as (
    select id, dt, dtName from cteDate
    WHERE dtName in( 'Saturday')
    )

    select datename(m,min(dt)) as month, min(dt) as first_saturday,max(dt) as last_saturday from cteSaturday
    group by year(dt)+ right('0'+month(dt),2)
    option (maxrecursion 0)

    Reply
  • February 22, 2016 at 11:01 am
    Permalink

    –update:

    DECLARE @fromD date = ‘2015-01-01’, @toD date = ‘2016-12-31’
    ;WITH cteDate as (
    SELECT ROW_NUMBER() OVER(ORDER BY (select null)) as id, @fromD as dt, DATENAME(dw, @fromD) as dtName
    UNION ALL
    SELECT id + 1 as id, DATEADD(day, 1, dt), DATENAME(dw, DATEADD(day, 1, dt)) dtName
    FROM cteDate
    WHERE dt < @toD
    )
    , cteSaturday as (
    select id, dt, dtName from cteDate
    WHERE dtName in( 'Saturday')
    )

    select year(dt),min(dt) as first_saturday,max(dt) as last_saturday from cteSaturday
    group by year(dt),year(dt)+ month(dt)
    order by 1
    option (maxrecursion 0)

    Reply

Leave a Reply