问题描述
我有一个存储过程，其中包含一个基于整数值的while循环，我必须循环while.ie，例如，如果一个月包含31天，则while循环应执行31次，或者一个月包含30天，wile循环将执行以下示例代码的30次我已写过，请对其进行修改.
声明@Days表(Days int)；
将@i声明为int；
声明@CurrentDate日期时间
声明@FirstDay datetime
声明@LastDay datetime
声明@ LastDay31 int
开始
设置@ CurrentDate =''2012-08-27''
设置@ CurrentDate =(选择REPLACE(@CurrentDate，''-''，''/''));
设置@ FirstDay =(SELECT CONVERT(VARCHAR(10)，DATEADD(dd，-(DAY(@CurrentDate)-1)，@ CurrentDate)，111))
设置@ FirstDay =(选择REPLACE(@FirstDay，''-''，''/''));
设置@ LastDay =(SELECT CONVERT(VARCHAR(10)，DATEADD(dd，-(DAY(DATEADD(mm，1，@ CurrentDate)))，DATEADD(mm，1，@ CurrentDate))，111))
设置@ LastDay =(选择REPLACE(@LastDay，''-''，''/''));
设置@ LastDay31 =(选择DAY(@LastDay))
if(LastDay31 = 31)
设置@i = 1
@i< 31
开始
插入@Days
值(@i);
设置@i = @i +1;
否则，如果
Hi,
I have a stored procedure that contains a while loop based on integer value i have to loop the while.i.e if a month contains 31 days the while loop should execute 31 times or month contains 30 days the wile loop executes 30 times the following sample code i have written please modify it.
Declare @Days Table (Days int);
Declare @i as int;
Declare @CurrentDate datetime
declare @FirstDay datetime
declare @LastDay datetime
declare @LastDay31 int
Begin
Set @CurrentDate= ''2012-08-27''
Set @CurrentDate=(select REPLACE(@CurrentDate,''-'',''/''));
set @FirstDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@CurrentDate)-1),@CurrentDate),111))
Set @FirstDay=(select REPLACE(@FirstDay,''-'',''/''));
set @LastDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@CurrentDate))),DATEADD(mm,1,@CurrentDate)),111))
Set @LastDay=(select REPLACE(@LastDay,''-'',''/''));
set @LastDay31=(select DAY(@LastDay))
if(LastDay31=31)
Set @i = 1
While @i < 31
Begin
Insert into @Days
Values(@i);
Set @i = @i + 1;
else if
if(LastDay31=30)
Set @i = 1
While @i < 30
Begin
Insert into @Days
Values(@i);
Set @i = @i + 1;
如果
结束结束；
end if
End;
推荐答案
Declare @Days Table (Days int);
Declare @i as int;
Declare @CurrentDate datetime
declare @FirstDay datetime
declare @LastDay datetime
declare @LastDay31 int
Set @CurrentDate= '2012-06-27'
Set @CurrentDate=(select REPLACE(@CurrentDate,'-','/'));
set @FirstDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@CurrentDate)-1),@CurrentDate),111))
Set @FirstDay=(select REPLACE(@FirstDay,'-','/'));
set @LastDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@CurrentDate))),DATEADD(mm,1,@CurrentDate)),111))
Set @LastDay=(select REPLACE(@LastDay,'-','/'));
set @LastDay31=(select DAY(@LastDay))
if(@LastDay31=31)
Begin
Set @i = 1
While @i <= 31
Begin
Insert into @Days Values(@i);
Set @i = @i + 1;
END
END
else if(@LastDay31=30)
begin
Set @i = 1
While @i <= 30
Begin
Insert into @Days Values(@i);
Set @i = @i + 1;
ENd
end
select * from @days
Declare @Days Table (Days int);
DECLARE @Date DATETIME
SET @Date = '2012-08-27'
DECLARE @Count INT
SET @Count = 1
DECLARE @DaysCount INT
SELECT @DaysCount = datediff(day, dateadd(day, 1-day(@date), @date),
dateadd(month, 1, dateadd(day, 1-day(@date), @date)))
WHILE @Count <= @DaysCount
BEGIN
INSERT INTO @Days
SELECT @Count
SET @Count = @Count + 1
END
SELECT * FROM @Days
第二种方法是创建一个函数，该函数将返回给定日期的天数.
找到了功能 [ ^ ]
The second approach would be to create a function that will return the number of days for a given date.
found the function here[^]
CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END
然后调用该函数以获取一个月中的天数，如下所示:
and then call the function to get the number of days in a month like this
Declare @Days Table (Days int);
DECLARE @i INT
SET @i = 1
WHILE @i <= (SELECT dbo.udf_GetNumDaysInMonth('2012-08-27') NumDaysInMonth)
BEGIN
INSERT INTO @Days
SELECT @i
SET @i = @i + 1
END
SELECT * FROM @Days
这篇关于如何在StoredProcedure中编写比较整数值的IF语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！