Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Conversion failed when converting date and/or time

Author  Topic 

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-11 : 13:17:46
Hi All,

I got this SP
but getting this fault "Conversion failed when converting date and/or time

CREATE PROCEDURE [dbo].[SP_Order_updateStatus_original]
(
@ordernr int,
@statusid int

)
As
declare @payment as datetime
DECLARE @counter AS INT
DECLARE @shipdate AS datetime =GETDATE()


SELECT @counter=COUNT(orderid) FROM T_Order_Main
WHERE OrderID = @Ordernr and shipdate ='19000101'


IF @counter=1
BEGIN
UPDATE T_Order_Main
SET shipdate = @shipdate
WHERE OrderID = @Ordernr

END
ELSE
BEGIN
INSERT INTO T_Order_main_shipdate
(orderid,Shipdate)
VALUES (@ordernr,@shipdate)
end


also tried
DECLARE @shipdate AS datetime =CONVERT(datetime,GETDATE(),105)

thanks

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-11 : 13:43:10
What is the datatype of shipdate in T_Order_Main and T_Order_main_shipdate tables?
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-11 : 13:55:38
aaah sorry datetime

Maybe it has something todo with country formating?
Go to Top of Page

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-11 : 14:10:59
sorry forget it.
I really do not know where my head is
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-11 : 14:14:26
Try the following change; if this doesnt work, would you be able to post your DDLs and some example data

quote:
Originally posted by mike13

Hi All,

I got this SP
but getting this fault "Conversion failed when converting date and/or time

CREATE PROCEDURE [dbo].[SP_Order_updateStatus_original]
(
@ordernr int,
@statusid int

)
As
declare @payment as datetime
DECLARE @counter AS INT
DECLARE @shipdate AS datetime =GETDATE()


SELECT @counter=COUNT(orderid) FROM T_Order_Main
WHERE OrderID = @Ordernr and shipdate = CAST('19000101' AS DATE)
IF @counter=1
BEGIN
UPDATE T_Order_Main
SET shipdate = @shipdate
WHERE OrderID = @Ordernr

END
ELSE
BEGIN
INSERT INTO T_Order_main_shipdate
(orderid,Shipdate)
VALUES (@ordernr,@shipdate)
end


also tried
DECLARE @shipdate AS datetime =CONVERT(datetime,GETDATE(),105)

thanks

Go to Top of Page
   

- Advertisement -