How to get max Satuday dates in a column of each month,without hardcoding...Can someone please help-CodePudding

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 is last Saturday of month.

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('09-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 * from GetDates where Day_Name = 'Saturday'

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