存储过程judge_sunday,返回当月有几个星期日,却实现不了.求大神门指点,不知道错在哪里.
菜鸟,写的不好勿喷,谢谢.ALTER PROCEDURE judge_sunday @count int output --判断当前月份有几个星期天
AS
DECLARE @date datetime;
DECLARE @year int,@month int;
DECLARE @lastdate datetime,@nextmonthFirstday datetime; --当前月份的最后一天,下个月第一天
DECLARE @startdate datetime; --当月第一天日期
DECLARE @lastnumber int; --当前月份的天数
DECLARE @i int;
DECLARE @weekday varchar(10);
DECLARE @table table(
date datetime,
weekday varchar(10));
set @date=getdate();
set @year=datename(year,@date);
set @month=datename(month,@date);
set @nextmonthFirstday=dateadd(month,1,rtrim(@year)+'-'+rtrim(@month)+'-'+'1');
set @lastdate=dateadd(day,-1,@nextmonthFirstday);
set @startdate=dateadd(day,-(@lastnumber-1),@lastdate);--@startdate值为null,why?所以改用月底时间往前推,但还是不行.
set @lastnumber=datename(day,@lastdate);
set @i = @lastnumber;
while @i<1
begin --从月底开始往前知道月初,求得每天是星期几
set @weekday=datename(weekday,dateadd(day,-1,@nextmonthFirstday));--@weekday也为null,不知为什么
insert @table select @lastdate,@weekday;
set @i=@i-1;
end
select @count=count(*) from @table where weekday='星期日';
return @count;
GOdeclare @count int;
exec @count=judge_sunday @count;
select @count存储过程求一个月有几个星期天求大神
菜鸟,写的不好勿喷,谢谢.ALTER PROCEDURE judge_sunday @count int output --判断当前月份有几个星期天
AS
DECLARE @date datetime;
DECLARE @year int,@month int;
DECLARE @lastdate datetime,@nextmonthFirstday datetime; --当前月份的最后一天,下个月第一天
DECLARE @startdate datetime; --当月第一天日期
DECLARE @lastnumber int; --当前月份的天数
DECLARE @i int;
DECLARE @weekday varchar(10);
DECLARE @table table(
date datetime,
weekday varchar(10));
set @date=getdate();
set @year=datename(year,@date);
set @month=datename(month,@date);
set @nextmonthFirstday=dateadd(month,1,rtrim(@year)+'-'+rtrim(@month)+'-'+'1');
set @lastdate=dateadd(day,-1,@nextmonthFirstday);
set @startdate=dateadd(day,-(@lastnumber-1),@lastdate);--@startdate值为null,why?所以改用月底时间往前推,但还是不行.
set @lastnumber=datename(day,@lastdate);
set @i = @lastnumber;
while @i<1
begin --从月底开始往前知道月初,求得每天是星期几
set @weekday=datename(weekday,dateadd(day,-1,@nextmonthFirstday));--@weekday也为null,不知为什么
insert @table select @lastdate,@weekday;
set @i=@i-1;
end
select @count=count(*) from @table where weekday='星期日';
return @count;
GOdeclare @count int;
exec @count=judge_sunday @count;
select @count存储过程求一个月有几个星期天求大神
ALTER PROCEDURE judge_sunday @count int output
as
begin
declare @startDate datetime,@endDate datetime,@numDays int--,@count int
set @startDate = DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
set @endDate = DATEADD(day,-1,DATEADD(mm,1,@startDate))
set @numDays = datediff(day, @startDate, @endDate) + 1;With NumDays as
(
select top(@numDays) row_number() over(order by (select 0)) as n from sys.objects
)
select @count=count(*) from NumDays
where DATEPART(WEEKDAY,convert(varchar(10), dateadd(day, NumDays.n - 1, @startDate), 120))=1
return @count
end--执行
declare @count int;
exec @count=judge_sunday @count;
select @count
set @a='2013-06-01';
with tb(a) as(
select dateadd(day,number,@a) from master..spt_values
where type='p' and number between 0 and 30
)
select count(1) from tb where datepart(a)=这里取星期日
and month(@a)=month(a) 上面的也行
set @a='2013-06-01';
with tb(a) as(
select dateadd(day,number,@a) from master..spt_values
where type='p' and number between 0 and 30
)
select count(1) from tb where datepart(WEEKDAY,a)=1
and month(@a)=month(a)
DECLARE @SampleDate DateTime, @MonthStart DateTime, @MonthEnd DateTime, @FirstSunday DateTime, @MaxSunday DateTime, @WeekCount INT -- For Testing different months
-- SET @SampleDate = CAST('03/05/2013' AS Datetime)SET @SampleDate = GETDATE()SET @MonthStart = CAST(CONVERT(varchar(2), Month(@SampleDate)) + '/01/' + CONVERT(varchar(4), Year(@SampleDate)) AS datetime) SET @MonthEnd =DATEADD(day,-1,DATEADD(month,1,@MonthStart))-- Assume the week starts on Sunday
SET @FirstSunday = DATEADD(day,7-DatePart(weekday,@MonthStart)+1, @MonthStart)
SET @MaxSunday = DATEADD(week, 4, @FirstSunday)
IF @MaxSunday > @MonthEnd
SET @WeekCount = 4
ELSE
SET @WeekCount = 5 PRINT @MonthStart
PRINT @MonthEnd
PRINT @FirstSunday
PRINT @MaxSunday
PRINT @WeekCount
IF @MaxSunday > @MonthEnd SET @WeekCount = 4
ELSE SET @WeekCount = 5
这个逻辑存在问题吧
IF @MaxSunday > @MonthEnd SET @WeekCount = 4
ELSE SET @WeekCount = 5
这个逻辑存在问题吧一个月最少是28天,最多是31天, 最起码都跨越4周,而且是4整周,最多跨越5周,可能会正好有5个周日。 自己验证一下吧。
(@count int output)
as
begin
select @count=count(1)
from master.dbo.spt_values
where [type]='P' and datediff(m,getdate(),dateadd(d,number,left(convert(varchar,getdate(),111),8)+'01'))=0
and datepart(dw,dateadd(d,number,left(convert(varchar,getdate(),111),8)+'01'))=1
end
declare @count int
exec judge_sunday @count output
select @count 'c'/*
c
-----------
4(1 row(s) affected)
*/
IF @MaxSunday > @MonthEnd SET @WeekCount = 4
ELSE SET @WeekCount = 5
这个逻辑存在问题吧一个月最少是28天,最多是31天, 最起码都跨越4周,而且是4整周,最多跨越5周,可能会正好有5个周日。 自己验证一下吧。
怎么是正好??29天才会正好有5哥周日,你的逻辑有问题,拿2013.9去测试一下就知道了.
补偿天数根据1号周几来:周日补6天,周1补0天,周2被1天,周3补2天,等等
declare @d1 date = '2013-3-1'
set @d1 = DATEADD(d, -day(@d1)+1, @d1) -- 当月1号
select ((5+DATEPART(weekday,@d1))%7+DATEDIFF(d,@d1,DATEADD(month, 1, @d1)))/7
补偿天数根据月末周几来:周日补6天,周1补5天,周2被4天,周3补3天,等等
declare @d1 date = '2013-6-4'
set @d1 = DATEADD(d,-1,DATEADD(month,1,DATEADD(d,-day(@d1)+1,@d1))) -- 本月最后一天
select @d1,(7-DATEPART(weekday,@d1)+DAY(@d1))/7
IF @MaxSunday > @MonthEnd SET @WeekCount = 4
ELSE SET @WeekCount = 5
这个逻辑存在问题吧一个月最少是28天,最多是31天, 最起码都跨越4周,而且是4整周,最多跨越5周,可能会正好有5个周日。 自己验证一下吧。
怎么是正好??29天才会正好有5哥周日,你的逻辑有问题,拿2013.9去测试一下就知道了.最少4个周日, 最多5个周日的逻辑没错。 错误出现在计算第一个周日。 修改如下:DECLARE @SampleDate DateTime, @MonthStart DateTime, @MonthEnd DateTime, @StartWeekDay INT, @FirstSunday DateTime, @MaxSunday DateTime, @WeekCount INT
-- For Testing different months
SET @SampleDate = CAST('09/05/2013' AS Datetime)
--SET @SampleDate = GETDATE()
SET @MonthStart = CAST(CONVERT(varchar(2), Month(@SampleDate)) + '/01/' + CONVERT(varchar(4), Year(@SampleDate)) AS datetime)
SET @MonthEnd =DATEADD(day,-1,DATEADD(month,1,@MonthStart))
-- Assume the week starts on Sunday
SET @StartWeekDay = DatePart(weekday,@MonthStart) SET @FirstSunday =CASE @StartWeekDay WHEN 1 THEN @MonthStart ELSE DATEADD(day,7-@StartWeekDay+1, @MonthStart) END SET @MaxSunday = DATEADD(week, 4, @FirstSunday)
IF @MaxSunday > @MonthEnd
SET @WeekCount = 4
ELSE
SET @WeekCount = 5 PRINT @MonthStart
PRINT @MonthEnd
PRINT @FirstSunday
PRINT @MaxSunday
PRINT @WeekCount