sql-按周输出每月的周日期范围

--日期参数，此处可以建立存储过程，接收月份，计算月开始结束时间或者直接接受开始与结束时间
declare @begDate datetime = '2014-06-01'
declare @endDate datetime = '2014-06-30'

declare @text varchar(max) = ''

while(@begDate <= @endDate)
begin
    select @text = @text + case 
                               when DATENAME(DW,@begDate) = '星期一'
                               then case 
                                        when convert(varchar(10),dateadd(dd,7,@begDate),120) <= @endDate
                                        then convert(varchar(10),@begDate,120) + '~' + convert(varchar(10),dateadd(dd,6,@begDate),120) + CHAR(10)
                                        else convert(varchar(10),@begDate,120) + '~' + convert(varchar(10),@endDate,120) + CHAR(10)
                                    end 
                               when DATENAME(DW,@begDate) != '星期一' and DATEPART(DD,@begDate) = 1
                               then convert(varchar(10),@begDate,120) + '~' + convert(varchar(10),dateadd(dd,case when 8 - DATEPART(dw,@begDate) < 7
                                                                                                                  then 8 - DATEPART(dw,@begDate)
                                                                                                                  else 0
                                                                                                             end,@begDate),120) + CHAR(10) 
                               else ''
                            end
                    
                    
    select @begDate = DATEADD(DD,1,@begDate)
end

print @text

 

转载于:https://www.cnblogs.com/zhoushangwu/p/4097069.html