How to get max Saturday dates in a column of each month in SQL Server. Can someone please help me.

Now I need only the dates which has last Saturday of month. For example,

The table has

07-08-2021 - Saturday
14-08-2021 - Saturday
21-08-2021 - Saturday
28-08-2021 - Saturday

04-09-2021 - Saturday
11-09-2021 - Saturday
18-09-2021 - Saturday
25-09-2021 - Saturday

Suppose we are in August month, I need to select last Saturday of that month( ONLY 28-08-2021) Suppose we are in September month, I need to select last Saturday of that month( ONLY 25-09-2021)

Output:

28-08-2021
25-09-2021

CodePudding user response：

assuming you have a datefield in your table (I will refer to it here as such in the below query)

with week as (
    select
      date_trunc('week', datefield   interval '2 day') - interval '2 day' as week_date
      -- ^this adjusts the week date_trunc to start on Saturday (it starts on Monday by default)
    
    from sometable  
)

select
    extract(month from week_date) as month_num,
    max(week_date) as last_saturday
    
from week
group by month_num

note: if you only have partial data for the current month, this query will need to be altered slightly, but you didn't give me a lot to go off of here

CodePudding user response：

A CTE is defined to populate the day name of entire month and then the requirement is filtered.

;with GetDates As (  
        select CAST('01-01-2021' as date) as StartDate, datename(dw, '09-01-2021') as Day_Name
        UNION ALL  
        select DATEADD(day,1, StartDate), datename(dw, DATEADD(day,1, StartDate))
        from GetDates  
        where StartDate < '09-30-2021'  
    )
    
    select max(StartDate)
    from GetDates where Day_Name = 'Saturday'
    group by month(StartDate)
    OPTION (MAXRECURSION 500)