07-获取一年中每个月最后一个星期五是几号

一、相关SQL

--1、在当前日期上加上1个月
select  dateadd(m,1,getdate())

select day(getdate())

--2、获取当前月最后一天
select dateadd(day,-day(getdate()),dateadd(m,1,getdate()))

--3、获取上个月最后一天
select dateadd(day,-day(getdate()),dateadd(m,0,getdate()))

--4、获取下个月最后一天
select dateadd(day,-day(getdate()),dateadd(m,2,getdate()))

--5、获取当前月最后一天是星期几
select  DATENAME(dw,dateadd(day,-day(getdate()),dateadd(m,1,getdate())))

--6、获取当前月最后一个星期五是几号
declare @week varchar(10)
declare @lastday varchar(20)
set @lastday=dateadd(day,-day(getdate()),dateadd(m,1,getdate()))
set @week=  DATENAME(dw,dateadd(day,-day(getdate()),dateadd(m,1,getdate())))
if @week ='星期一' 
begin
   select dateadd(day,-2,@lastday)
end
else if @week ='星期二' 
begin
   select dateadd(day,-3,@lastday)
end
else if @week ='星期三' 
begin
   select dateadd(day,-4,@lastday)
end
else if @week ='星期四' 
begin
   select dateadd(day,-5,@lastday)
end
else if @week ='星期五' 
begin
   select @lastday
end
else if @week ='星期六' 
begin
   select dateadd(day,-1,@lastday)
end
else if @week ='星期日' 
begin
   select dateadd(day,-2,@lastday)
end

二、实现的脚本

思路：

（1）先获取每月的最后一天以及最后一天是星期几，存放在一张临时表中；

（2）根据最后一天是星期几的结果做if判断，来减去对应的数值，并获取每月的星期五是几号；

declare @currerMonth int
declare @currerDay int

create table #lastDay(monthd int ,lastday varchar(100),lastdayweek varchar(20))
--获取当前月份
set @currerMonth = month(getdate())

--获取当前日
set @currerDay = day(getdate())

--1、获取每月的最后一天
declare @i varchar(10)
set @i=1
while(@i <=12)
begin
    declare @sql varchar(max)
    declare @diffmonth varchar(10)
    set @diffmonth = @i - @currerMonth +1
    set @sql ='insert into #lastDay values('+@i+',
              dateadd(day,-day(getdate()),dateadd(m,'+@diffmonth+',getdate())),
              DATENAME(dw,dateadd(day,-day(getdate()),dateadd(m,'+@diffmonth+',getdate())))
              )'
    exec(@sql)
    --print @sql
    set @i =@i +1
end


--2、获取每月的最后一个星期五
create table #friDay(monthd int ,dayd varchar(100))
declare @sql1 varchar(max)
declare @monthd varchar(10)
declare @lastDate varchar(100)
declare @week varchar(10)
declare cur CURSOR for
select monthd from #lastDay
open cur
fetch next from cur into @monthd
while @@FETCH_STATUS = 0
begin
        select @week=lastdayweek  from #lastDay where monthd=@monthd
        select @lastDate=lastday  from #lastDay where monthd=@monthd
    --select @lastDate
        if @week ='星期一' 
        begin
            set @sql1 = 'insert into #friDay values('+@monthd+',dateadd(day,-2,'''+@lastDate+'''))'
            exec ( @sql1)
            --print @sql1
        end
        else if @week ='星期二' 
        begin
            set @sql1 = 'insert into #friDay values('+@monthd+',dateadd(day,-3,'''+@lastDate+'''))'
            exec ( @sql1)
        end
        else if @week ='星期三' 
        begin
           set @sql1 = 'insert into #friDay values('+@monthd+',dateadd(day,-4,'''+@lastDate+'''))'
           exec ( @sql1)
        end
        else if @week ='星期四' 
        begin
           set @sql1 = 'insert into #friDay values('+@monthd+',dateadd(day,-5,'''+@lastDate+'''))'
            exec ( @sql1)
        end
        else if @week ='星期五' 
        begin
            set @sql1 = 'insert into #friDay values('+@monthd+','''+@lastDate+''')'
            exec ( @sql1)
        end
        else if @week ='星期六' 
        begin
           set @sql1 = 'insert into #friDay values('+@monthd+',dateadd(day,-1,'''+@lastDate+'''))'
            exec ( @sql1)
        end
        else if @week ='星期日' 
        begin
           set @sql1 = 'insert into #friDay values('+@monthd+',dateadd(day,-2,'''+@lastDate+'''))'
            exec ( @sql1)
            --print @sql1
        end

    fetch next from cur into @monthd
end
close cur
deallocate cur

select  monthd,convert(datetime,dayd) as friday from #friDay


drop table #lastDay
drop table #friDay

结果如下：