SET QUOTED_IDENTIFIER ON
GO
GO
/*****************************************************************************
Name: Proc_Init_Sys_PlanPeriod
Descr: 初始化系统计划期间信息
Params: @StartDate 开始日期
@EndDate 结束日期
@SettlementDay 结算日
Tables: Sys_PlanPeriod
Example: Exec Proc_Init_Sys_PlanPeriod '2010-1-1 0:0:0','2010-12-31 0:0:0',21
SELECT * FROM Sys_PlanPeriod
WHERE StartTime Between '2010-1-1 0:0:0' AND '2010-12-31 0:0:0'
******************************************************************************/
ALTER PROCEDURE Proc_Init_Sys_Planperiod(
@StartDate DATETIME,
@EndDate DATETIME,
@SettlementDay INT)
AS
DECLARE
@PeriodNo CHAR(8) --结算期间编号
DECLARE
@AddEndDate DATETIME --月结束日期
BEGIN
-- 初始化开始日期
SET @StartDate = CONVERT(datetime,CONVERT(VARCHAR(6),DATEADD(MM,-1,@StartDate),112) + CONVERT(VARCHAR,@SettlementDay),
120)
SET @AddEndDate = @StartDate
BEGIN TRANSACTION --开始事务
WHILE (@AddEndDate < @EndDate)
BEGIN
--初始化结算期间编号
SET @PeriodNo = CONVERT(VARCHAR(6),@StartDate,112)
--修改结束日期
SET @AddEndDate = DATEADD(ms,-3,DATEADD(dd,1,DATEADD(MM,1,@StartDate)))
DELETE FROM Sys_Planperiod
WHERE Periodno = @PeriodNo
INSERT INTO Sys_Planperiod(periodno,
starttime,
endtime,
createuser,
createtime)
VALUES (@PeriodNo,
@StartDate,
@AddEndDate,
1,
GETDATE())
--设置下个月开始日期
SET @StartDate = DATEADD(mm,1,@StartDate)
END
IF @@ERROR <> 0
ROLLBACK TRANSACTION --回滚事务
ELSE
COMMIT TRANSACTION --提交事务
END
GO
SET QUOTED_IDENTIFIER off
GO
SET ANSI_NULLS ON
GO