SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Conversion failed when converting date and/or time
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 05/11/2013 :  13:17:46  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
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

547 Posts

Posted - 05/11/2013 :  13:43:10  Show Profile  Reply with Quote
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

Netherlands
214 Posts

Posted - 05/11/2013 :  13:55:38  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
aaah sorry datetime

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

mike13
Posting Yak Master

Netherlands
214 Posts

Posted - 05/11/2013 :  14:10:59  Show Profile  Click to see mike13's MSN Messenger address  Reply with Quote
sorry forget it.
I really do not know where my head is
Go to Top of Page

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/11/2013 :  14:14:26  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000