日期:2014-05-18  浏览次数:20701 次

一模一样的的存储过程,执行出来一个正确一个提示"从字符串转换日期和/或时间时,转换失败"
存储过程1:
SQL code
createPROCEDURE [dbo].[StorSaleReportDateSet] 
    -- Add the parameters for the stored procedure here

    @StartTime dateTime,
    @EndTime dateTime,
    @flag int = 0 output
    
AS 
BEGIN

SELECT
product.productCode,
product.productName,
max(product.unitName) as unitName,
SUM(proStorBill.quantity) as StorQuantity,
SUM(proSaleBill.quantity) as SaleQuantity,
AVG(product.storQuantity) as remainQuantity

from product left  join proStorBill on product.productName=proStorBill.productName 
  and proStorBill.storDate is not null and proStorBill.storDate<@EndTime and proStorBill.storDate>@StartTime
  left  join proSaleBill  on  product.productName=proSaleBill.productName
  and  proSaleBill.saleDate is not null and proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime
  
  where  (proStorBill.storDate<@EndTime and proStorBill.storDate>@StartTime) or
     (proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime)
  
  group by product.productName,product.productCode

    if (@@ROWCOUNT>0)
                    Set @flag = 1
                else
                    Set @flag = 0

END

执行:
SQL code
exec [StorSaleReportDateSet] '2012/5/27 0:00:00','2012/6/30 0:00:00'

结果正确:

第二个存储过程:
SQL code
create PROCEDURE [dbo].[receivableReportDateSet] 
    -- Add the parameters for the stored procedure here
    @StartTime dateTime,
    @EndTime dateTime,
    @flag int = 0 output
    
AS 
BEGIN

SELECT
busiPart.busiPartCode,
busiPart.busiPartName,
SUM(proSaleBill.totalPrice) as totalPrice,
SUM(receiptBill.realReciAmount) as realReciAmount,
AVG(busiPart.creditUsed) as creditUsed

from busiPart left  join proSaleBill on busiPart.busiPartName=proSaleBill.BusiName 
  and proSaleBill.saleDate is not null and proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime
  left  join receiptBill  on  busiPart.busiPartName=receiptBill.receDate
  and  receiptBill.receDate is not null and receiptBill.receDate<@EndTime and receiptBill.receDate>@StartTime
  
  where  (proSaleBill.saleDate<@EndTime and proSaleBill.saleDate>@StartTime) or
     (receiptBill.receDate<@EndTime and receiptBill.receDate>@StartTime)
  
  group by busiPart.busiPartName,busiPart.busiPartCode

    if (@@ROWCOUNT>0)
                    Set @flag = 1
                else
                    Set @flag = 0

END

执行:
SQL code
exec [receivableReportDateSet] '2012/5/27 0:00:00','2012/6/30 0:00:00'

错误提示:

存储过程几乎是一样的,只是表换了,传的参数也是一模一样。但是一个执行结果正确,另一个却提示“从字符串转换日期和/或时间时,转换失败”。 小弟实在不解,恳请各位大侠解答,万分感谢

------解决方案--------------------
SQL code

TRY 修改第二个存储过程
CREATE PROCEDURE [dbo].[Receivablereportdateset]
  -- Add the parameters for the stored procedure here
  @StartTime DATETIME,
  @EndTime   DATETIME,
  @flag      INT = 0 output
AS
  BEGIN
      SELECT busiPart.busiPartCode,
             busiPart.busiPartName,
             Sum(proSaleBill.totalPrice)     AS totalPrice,
             Sum(receiptBill.realReciAmount) AS realReciAmount,
             Avg(busiPart.creditUsed)        AS creditUsed
      FROM   busiPart
             LEFT JOIN proSaleBill
                    ON busiPart.busiPartName = proSaleBill.BusiName
                       AND proSaleBill.saleDate IS NOT NULL
                       AND proSa