写了一个存储过程,返回当月有几个星期天,却实现不了
存储过程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;
GO
declare @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
[解决办法]
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
