毫无二致的的存储过程，执行出来一个正确一个提示"从字符串转换日期和/或时间时，转换失败"
日期：2014-05-18 浏览次数：20686 次
一模一样的的存储过程，执行出来一个正确一个提示"从字符串转换日期和/或时间时，转换失败"
存储过程1：
执行：
结果正确：
第二个存储过程：
执行：
错误提示：
存储过程几乎是一样的，只是表换了，传的参数也是一模一样。但是一个执行结果正确，另一个却提示“从字符串转换日期和/或时间时，转换失败”。 小弟实在不解，恳请各位大侠解答，万分感谢
------解决方案--------------------
存储过程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
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
