Time format error | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Time format error

<br /><br />i have one problem in converting char to smalldatetime<br />that is 000002—&gt;convert into HH;MM;SS<br /><br />declare @time varchar(30),<br />set @time=’000002′<br />select right(cast(left(@time,2) + ‘:’ +substring(@time,3,2) + ‘:’ +right(@time,2) as smalldatetime),<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /><br /><br />this above code working fine…<br />if assignment like that<br />set @time=000002 means got error<br /><br />in my program i’m using variable so use that<br /> select right(cast(left(@time,2) + ‘:’ +substring(@time,3,2) + ‘:’ +right(@time,2) as smalldatetime),<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br /> that query show error<br />Syntax error converting character string to smalldatetime data type.<br />any one help<br /><br /><br />
Please don’t ask the same question more than once!
See my answer here:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=22172 The error is because you don’t aasign a character expression to @time but rather a numerical value which SQL Server is likely to evaluate as an INT value, which has a higher precedence than varchar and therefore raises the error.

Frank Kalis
Microsoft SQL Server MVP
Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com
Webmaster:http://www.insidesql.de
]]>