Error converting datatype varhcar to datetime & stored prcedure

Last Post 27 Jul 2004 02:35 PM by cari. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Eddy_K
New Member
New Member

--
19 Jul 2004 09:22 AM
I'm having a problem with a var being passed into a stored procedure. I set a variable with a value from a db table. var = “2004-07-19 11:01:56.000”. I then take this var and pass it into the sp;

sql = spRun & " @ProductId=' " & PId & " ', @mydate=' " & var & " ' "

The weird thing is, this stuff has worked for a while and now it isn't. Anyone have any ideas?
It blows up on me stating the datatype conversion error. @mydate in the sp is a datetime.

thanks,
EK
cari
New Member
New Member

--
27 Jul 2004 02:35 PM
What I do in those Stored procedures where i maipulate datetimes is this:

ej:
tb Client

ID NAME BIRTH_DATE

1 AAA 1934-04-15
2 BBB 1974-06-21
3 CCC 1948-10-25

(column type of birth_date is datetime)


So when you use this column into your SPs try to convert the column always in the same way. I often use a varchar convertion.

For example:
(@date_BRTH is the parameter )

"
Create procedure sp_alta_estudio_pac (@pac int, @date_BRTH varchar(10), @r int OUTPUT)
as
set nocount on
declare @f1 datetime, @err int, @id int

if @date_BRTH=''
set @date_BRTH1=null
else
set @date_BRTH1=convert(datetime, @date_BRTH,103);


--inserto en la tb estudio_pac al paciente con los datos dados
insert into estudio_pac (fecha, id_paciente) values (@date_BRTH1, @pac)

set @err=@@error

if (@err=0)
begin

--Seteo datos para ejecutar el sp_GRABO_LOG2 ()
set @id=(select id from estudio_pac where fecha=@date_BRTH1 and id_paciente=@pac)

set @r=@id
end
else
set @r=0;



set nocount off
RETURN
GO
"
So if you always convert it into varchar, you can have same thing to compare.


You are not authorized to post a reply.

Acceptable Use Policy