账套升级报错，从 nvarchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值

yhb6688

数据库名: UFDATA_777_2014
D:\U8SOFT\Admin\SQLFILE12100\Main\Ufdata\data\NL\DATA_DATA_FA_MIX_NL.sql
错误信息:
-2147217913
从 nvarchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。
执行如下语句时出错:


---同步升级备份表，避免升级后反月结造成预置数据丢失的情况
---得到当前数据库的年度


declare @iYearJZ int
declare @iMonthJZ tinyint
declare @iMaxMonthJZ tinyint
declare @dWritableDate DateTime
declare @iflag as int
select @iflag = COUNT(*) from UFSystem..UA_Account_sub
where cAcc_Id= SUBSTRING(DB_NAME(),8,3) and iYear= '9999' and ISNULL(dsubsysused,'')<>''
and csub_id = 'fa'
if @iflag = 1
begin
  select @dWritableDate=  isnull(cValue,'1900-1-1') from AccInformation  where cSysID  = 'fa' and cName = 'dWritableDate'
  SELECT @iYearJZ= iYear,@iMaxMonthJZ = iId from UFSystem..ua_period P inner join UFSystem..UA_AccountDatabase D
  on P.cAcc_Id = D.cAcc_Id WHERE cDatabase = DB_NAME()
  and @dWritableDate between dbegin and dEnd
   
  --print @iYearJZ
  --print @iMaxMonthJZ
  set @iMonthJZ = 1
  WHILE @iMonthJZ <= @iMaxMonthJZ
  Begin
    begin
           IF NOT EXISTS (select cname from UFFA_BAK_AccInformation where cname = 'bControlByDept' and cSysID = 'FA' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      begin
       insert into UFFA_BAK_AccInformation(csysid,cid,cname,ccaption,ctype,cValue,cdefault,bvisible,benable,iyearJZ,imonthJZ)
       values('FA','111','bControlByDept','按照部门控制权限','bBoolean','false','false',0,0,@iYearJZ,@iMonthJZ)
      end
         
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_AccInformation where cName = 'bTitlePeriod' and cSysID = 'FA' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      begin
       insert into UFFA_BAK_AccInformation (cSysID,cID,cName,cCaption,cType,cValue,cDefault,bVisible,bEnable,iyearJZ,imonthJZ)
       values('FA',106,'bTitlePeriod','折旧清单标题是否显示期间','boolen',0,0,0,0,@iYearJZ,@iMonthJZ)
      end
     if not exists (SELECT sName from uffa_bak_fa_Items where sName = N'sSeriesNum' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into uffa_bak_fa_Items ( [sID],[sName],[sCaption],[sEngCaption],[sSysCaption],[sEngSysCaption],[bVisible],[bDel],[bMove],[bIsValue],[bAutoPop],
             [bQuery],[lQueryOrder],[sType],[bDepr], [bEvn],[sSourceTable],[bEdit],[bAllowNull],[iIntLength],[iPointLength],[memFormula],[memUsedItems],
             [memItemsUseMe],[iDictType],[iDefineType],[lByUsed],[sHelpID],[sRemark],[sLockedBy],iyearJZ,imonthJZ)
       values(264,'sSeriesNum','序列号',NULL,'序列号',NULL,1,1,1,0,0,1,NULL,'sString',0,1,'fa_Cards.sSeriesNum',1,1,400,-1,NULL,NULL,NULL,16,0,10,NULL,NULL,NULL,@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sName from uffa_bak_fa_Items where sName = N'sCommodityCode'  and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into uffa_bak_fa_Items ( [sID],[sName],[sCaption],[sEngCaption],[sSysCaption],[sEngSysCaption],[bVisible],[bDel],[bMove],[bIsValue],[bAutoPop],
             [bQuery],[lQueryOrder],[sType],[bDepr], [bEvn],[sSourceTable],[bEdit],[bAllowNull],[iIntLength],[iPointLength],[memFormula],[memUsedItems],
             [memItemsUseMe],[iDictType],[iDefineType],[lByUsed],[sHelpID],[sRemark],[sLockedBy],iyearJZ,imonthJZ)
       values(265,'sCommodityCode','商品码',NULL,'商品码',NULL,1,1,1,0,0,1,NULL,'sString',0,1,'fa_Cards.sCommodityCode',1,1,400,-1,NULL,NULL,NULL,16,0,10,NULL,NULL,NULL,@iYearJZ,@iMonthJZ)
      END
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_fa_ItemsOfQuery where sname = 'sSeriesNum' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_ItemsOfQuery(iQuerysType,sName,iyearJZ,imonthJZ)
       values(1,'sSeriesNum',@iYearJZ,@iMonthJZ)
      END
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_fa_ItemsOfQuery where sname = 'sCommodityCode' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_ItemsOfQuery(iQuerysType,sName,iyearJZ,imonthJZ)
       values(1,'sCommodityCode',@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sIDKey from UFFA_BAK_fa_CardOfPUColSet where sIDKey = N'cVenCode' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_CardOfPUColSet (iFun,sIDKey,iColPos,bFixed,bDisp,bMustSel,iyearJZ,imonthJZ)
       values(2,'cVenCode',33,1,0,0,@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sIDKey from UFFA_BAK_fa_CardOfPUColSet where sIDKey = N'cVenName' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_CardOfPUColSet (iFun,sIDKey,iColPos,bFixed,bDisp,bMustSel,iyearJZ,imonthJZ)
       values(2,'cVenName',34,1,0,0,@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sIDKey from UFFA_BAK_fa_CardOfPUColSet where sIDKey = N'dPurDate' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_CardOfPUColSet (iFun,sIDKey,iColPos,bFixed,bDisp,bMustSel,iyearJZ,imonthJZ)
       values(2,'dPurDate',35,1,0,0,@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sIDKey from UFFA_BAK_fa_CardOfPUColSet where sIDKey = N'sSeriesNum' and iFun =2 and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_CardOfPUColSet (iFun,sIDKey,iColPos,bFixed,bDisp,bMustSel,iyearJZ,imonthJZ)
       values(2,'sSeriesNum',36,1,0,0,@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sIDKey from UFFA_BAK_fa_CardOfPUColSet where sIDKey = N'sCommodityCode' and iFun =2 and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_CardOfPUColSet (iFun,sIDKey,iColPos,bFixed,bDisp,bMustSel,iyearJZ,imonthJZ)
       values(2,'sCommodityCode',37,1,0,0,@iYearJZ,@iMonthJZ)
      END
     if not exists (SELECT sIDKey from UFFA_BAK_fa_CardOfPUColSet where sIDKey = N'sCommodityCode' and iFun = 1 and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      BEGIN
       insert into UFFA_BAK_fa_CardOfPUColSet (iFun,sIDKey,iColPos,bFixed,bDisp,bMustSel,iyearJZ,imonthJZ)
       values(1,'sCommodityCode',20,1,0,0,@iYearJZ,@iMonthJZ)
      END
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_AccInformation where cName = 'bInvCardCanEdit' and cSysID = 'FA' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      begin
       insert into UFFA_BAK_AccInformation (cSysID,cID,cName,cCaption,cType,cValue,cDefault,bVisible,bEnable,iyearJZ,imonthJZ)
       values('FA','107','bInvCardCanEdit','采购资产卡片原值可编辑','bBoolean','False','FALSE',0,0,@iYearJZ,@iMonthJZ)
      end
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_AccInformation where cName = 'iVoucherNumLen' and cSysID = 'FA' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      begin
       insert into UFFA_BAK_AccInformation (cSysID,cID,cName,cCaption,cType,cValue,cDefault,bVisible,bEnable,iyearJZ,imonthJZ)
       values('FA',108,'iVoucherNumLen','变动单编号长度','iInteger',5,5,0,0,@iYearJZ,@iMonthJZ)
      end
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_AccInformation where cName = 'bAllowUnequalityFS' and cSysID = 'FA' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      begin
       insert into UFFA_BAK_AccInformation (cSysID,cID,cName,cCaption,cType,cValue,cDefault,bVisible,bEnable,iyearJZ,imonthJZ)
       values('FA',109,'bAllowUnequalityFS','本期发生额对账不平是否限制月末结账','bBoolean','TRUE','TRUE',0,0,@iYearJZ,@iMonthJZ)
      end
     IF not EXISTS (SELECT top 1 * FROM UFFA_BAK_AccInformation where cName = 'bAllowUnequalityQM' and cSysID = 'FA' and iyearJZ =@iYearJZ  and imonthJZ = @iMonthJZ)
      begin
       insert into UFFA_BAK_AccInformation (cSysID,cID,cName,cCaption,cType,cValue,cDefault,bVisible,bEnable,iyearJZ,imonthJZ)
       values('FA',110,'bAllowUnequalityQM','期末余额对账不平是否限制月末结账','bBoolean','TRUE','TRUE',0,0,@iYearJZ,@iMonthJZ)
      end
    end
   set @iMonthJZ=@iMonthJZ+1
  End  
End