SQL Server Performance Forum – Threads Archive
char to smalldatetime format
<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 helpdeclare @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 @[email protected]<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.
Did you try the suggested methods? Madhivanan Failing to plan is Planning to fail
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
]]>