Conversion failed when converting date and/or time

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?

mike13
Posting Yak Master

219 Posts

Posted - 2013-05-11 : 13:55:38

aaah sorry datetime

Maybe it has something todo with country formating?

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources