SQL Server Performance

Time format error

Discussion in 'Getting Started' started by rv62, Jun 14, 2007.

  1. rv62 New Member

    <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 />
  2. FrankKalis Moderator

    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

Share This Page