SQL Server Performance

char to smalldatetime format

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

  1. rv62 New Member

    <br /><br />i have one problem <br /><br />i want to convert char '164910' to '16:49:10' time format that's hh:mm<img src='/community/emoticons/emotion-7.gif' alt=':s' />s<br /><br />i need query for that<br />i tried that but i got <br />error<br />The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.<br /><br />any one help
  2. dineshasanka Moderator

    declare @charDate varchar(30)
    Set @charDate = '164910'


    select cast (left(@charDate,2) + ':' +substring(@charDate,3,2) + ':' +right(@chardate,2) as smalldatetime)

    ----------------------------------------
    Contributing Editor, Writer & Forums Moderator
    http://www.SQL-Server-Performance.Com

    Visit my Blog at
    http://dineshasanka.spaces.live.com/
  3. FrankKalis Moderator

    Just for fun, you can also do something like:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @str CHAR(6)<br />SET @str = '164910'<br />SELECT @str / 10000 , (@str % 10000) /100, ((@str % 10000) % 100)<br /> , DATEADD(SECOND, (@str / 10000) * 3600 +(@str % 10000) /100 * 60 + ((@str % 10000) % 100), 0)<br /></font id="code"></pre id="code"><br />But Dinesh's code is probably more readable. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br />Contributing Editor, Writer & Forum Moderator<a target="_blank" href=http://www.sql-server-performance.com>http://www.sql-server-performance.com</a><br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  4. rv62 New Member

    that data can be fetched from table <br />while trying this code i got error<br /><br />declare @newtime varchar(20),@ttime varchar(20)<br />set @newtime=@time<br />select @ttime= right(cast(left(@newtime,2) + ':' +substring(@newtime,3,2) + ':' +right(@newtime,2) as smalldatetime),<img src='/community/emoticons/emotion-11.gif' alt='8)' />*/<br /><br />yntax error converting character string to smalldatetime data type.
  5. Madhivanan Moderator

    Did you try the suggested methods?

    Madhivanan

    Failing to plan is Planning to fail
  6. FrankKalis Moderator

  7. Madhivanan Moderator

    If that is the case, append 0 in front of it

    right('000000'+col,6)

    and use this in that expression

    Madhivanan

    Failing to plan is Planning to fail

Share This Page