一个存储过程执行的时候 提示日期转化异常

一个存储过程执行的时候 提示日期转化错误！

SQL code

ALTER  procedure [dbo].[P_PromotionAnalysisBBB]--促销分析Promotion analysis    (      @BeginDate datetime,--开始日期      @EndDate datetime --结束日期    )AS     beginselect                PromTime,--促销时间               ShopName,--店铺名称               PromotionName,--活动名称               GiftItemName,--促销礼品               sum(Cost),--成本价格               sum(OriginalPrice),--原价               sum(SalesPrice),--销售价格               sum(DiscountFee),--折扣费用               sum(Qty)--促销数量               from          (select                         Convert(date,PromTime) as PromTime,                       null as ShopName,                       null as PromotionName,                       null as GiftItemName,                       0 as cost,                       0 as OriginalPrice,                       0 as SalesPrice,                       0 as DiscountFee,                       0 as Qty                       from dbo.Sales_Order--订单销售表               where                        PromTime > CONVERT(DATE, @BeginDate)and PromTime < CONVERT(DATE, DATEADD(day, 1, @EndDate))               group by CONVERT(DATE, PromTime)               union all               select                         0  as ShopId,                       null as ShopName,                       GETDATE() as PromTime,                       PromotionName as PromotionName,                       GiftItemName  as GiftItemName,                       0  as cost,                       0  as OriginalPrice,                       0  as SalesPrice,                       DiscountFee   as DiscountFee,                       0  as Qty              from Top_PromotionDetail--促销表              group by GETDATE(),PromotionName               union all               select  0   as ShopId,                       null  as ShopName,                       GETDATE()  as PromTime,                       null  as PromotionName,                       null  as GiftItemName,                       sum(cost)  as cost,                       sum(OriginalPrice) as OriginalPrice,                       sum(SalesPrice)    as SalesPrice,                       0                  as DiscountFee,                       count(Qty)         as Qty               from Sales_OrderItem--订单销售详细表                              union all               select                         ShopName  as ShopName,                       GETDATE()  as PromTime,                       null  as PromotionName,                       null  as GiftItemName,                       0    as cost,                       0    as OriginalPrice,                       0    as SalesPrice,                       0    as DiscountFee,                       0        as Qty               from Top_Shop--店铺表               )a             group by CONVERT(DATE, PromTime),ShopName ,PromotionName,GiftItemName    end

DECLARE@return_value int

EXEC@return_value = [dbo].[P_PromotionAnalysisBBB]
@BeginDate = N'2012-1-1',
@EndDate = N'2012-2-2'

SELECT'Return Value' = @return_value

GO
消息 241，级别 16，状态 1，过程 P_PromotionAnalysisBBB，第 9 行
从字符串转换日期和/或时间时，转换失败。


[解决办法]
PromTime是不是有非日期型的
select isdate(PromTime)
from dbo.Sales_Order
有没有0
[解决办法]
日期有格式不对的，转换失败了。
[解决办法]
@BeginDate 传进来的值是否不是日期格式
[解决办法]

探讨

引用:
日期有格式不对的，转换失败了。


晕了！地球人都知道是日期格式不对！请解决！

------解决方案--------------------

分别执行以下三段 union all 的代码，看看问题出在哪里
[解决办法]
你自己可以将那些时间全都Print @PromTime 出来，看看是哪步的时间转换出错了。
[解决办法]
加在你的select语句里啊，子查询里的select语句
[解决办法]
把你的语句单独放到SSMS查询窗口中，然后将两个传入参数直接改为2个日期值，运行下看看，如果有出错，SQL会提示第几行的。
[解决办法]
你先把 dbo.Sales_Order--订单销售表
的PromTime,--促销时间
时间格式规范

查看更多 下一篇