<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
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/
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>
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.
The error in Dinesh's suggestion is probably due to data like '64910' with only five characters instead of six. -- Frank Kalis Microsoft SQL Server MVP Contributing Editor, Writer & Forum Moderatorhttp://www.sql-server-performance.com Webmaster:http://www.insidesql.de
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
