Question

sql中怎么根据年月统计月的周数

Answer 1

DECLARE @year int,@month int,@tempdata datetime,@sumday moneySELECT @year=2015,@month=11--先拼接成参数月份的第一天SELECT @tempdata=convert(varchar(4),@year)+'-'+convert(varchar(2),@month)+'-01'--统计参数月份总天数SELECT @sumday=datediff(day,@tempdata,dateadd(month,1,@tempdata))--计算参数月份一共几周SEELCT ceiling((@sumday-(case when datename(weekday,@tempdata)='星期一' then 7.0 when datename(weekday,@tempdata)='星期二' then 6.0 when datename(weekday,@tempdata)='星期三' then 5.0 when datename(weekday,@tempdata)='星期四' then 4.0 when datename(weekday,@tempdata)='星期五' then 3.0 when datename(weekday,@tempdata)='星期六' then 2.0 else 1.0 end))/7.0)+1