毫无二致的的存储过程，执行出来一个正确一个提示"从字符串转换日期和/或时间时，转换失败"

一模一样的的存储过程，执行出来一个正确一个提示"从字符串转换日期和/或时间时，转换失败"
存储过程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 proSaleBill.saleDate < @EndTime
                       AND proSaleBill.saleDate > @StartTime
             LEFT JOIN receiptBill
                    ON busiPart.busiPartName = receiptBill.receDate
                       AND receiptBill.receDate IS NOT NULL
                       AND receiptBill.receDate < CONVERT(VARCHAR(32),@EndTime,120)
                       AND receiptBill.receDate > CONVERT(VARCHAR(32),@StartTime,120)
      WHERE  ( proSaleBill.saleDate < @EndTime
               AND proSaleBill.saleDate > @StartTime )
              OR ( receiptBill.receDate < CONVERT(VARCHAR(32),@EndTime,120)
                   AND receiptBill.receDate > CONVERT(VARCHAR(32),@StartTime,120) )
      GROUP  BY busiPart.busiPartName,
                busiPart.busiPartCode

      IF ( @@ROWCOUNT > 0 )
        SET @flag = 1
      ELSE
        SET @flag = 0
  END

毫无二致的的存储过程，执行出来一个正确一个提示"从字符串转换日期和/或时间时，转换失败"

相关推荐