May 19, 2004 at 10:51 pm
i have written a sTored Procedure
and in that procedure there is a line of code, which is
--------------------------------
declare @insertSql varchar(5000)
declare @subject varchar(250)
declare @TodyDate datetime
set @TodyDate=getdate()
print @todyDate 'gives me May 20 2004 10:17AM
set @subject='Pending Complaint'
set @insertSql='Insert into RMCPL_MAIL_HD(RMCPL_DT,RMCPL_SUB,rmcpl_usr) values('+@todyDate+','+@subject +','+@usrcode
print @insertSql
-----------------
but iam getting this error
Server: Msg 241, Level 16, State 1, Procedure spCheckPendingComplaints, Line 45
Syntax error converting datetime from character string.
now what do i do about this, previously i was datatype as varchar(10), it gave this error and again i changed the datatype to datetime same error again, any solutions
Thanks in Advance
<a href="http://www.websolsoftware.com"> For IT jobs click here</a>
*Sukhoi*[font="Arial Narrow"][/font]
May 20, 2004 at 1:13 am
You set @insertSql statement is trying to concatenate a string and a date, and therefore throws the error.
If you want to build the string then simply do the following
set @insertSql='Insert into RMCPL_MAIL_HD(RMCPL_DT,RMCPL_SUB,rmcpl_usr) values('+CAST(@todyDate AS VARCHAR(10))+','+@subject +','+@usrcode
I am guessing that when your @todyDate was a VARCHAR(10) the problem occurred in the set @TodyDate=getdate() statement because you are trying to store the date into a VARCHAR.
I don't know the context of your procedure but personally I would not build up the dynamic SQL you are using. I would simply write it as
INSERT RMCPL_MAIL_HD (
RMCPL_DT,
RMCPL_SUB,
rmcpl_usr)
VALUES(
GETDATE() ,
@subject ,
@usrcode)
Dates are always a nightmare in SQL. Quite often I have to use the CONVERT statement (see books on line for formats) in order to make sure that conversions from text values to dates and vice verse works OK.
May 21, 2004 at 8:50 am
Change CAST(@todyDate AS VARCHAR(10) to CONVERT(Varchar, @todyDate, 101) should work.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply