Try this:
DECLARE @cYour_Time char(7)
SET @cYour_time = '14567'
/* assume, that the @cYour_time is 7 characters long */
SET @cYour_time = SPACE( 7 - LEN( @cYour_time ) ) + @cYour_time
SELECT CONVERT( char(8 ),
CAST(
CAST( LEFT( @cYour_time, 4 ) / 60 AS char(2) ) + ':' + -- number of hours
CAST( LEFT( @cYour_time, 4 ) - ( LEFT( @cYour_time, 4 ) / 60 ) * 60 AS char(2) ) + ':' + -- number of minutes
CAST( ROUND( SUBSTRING( @cYour_time, 5, 2 ) + '.' + RIGHT( @cYour_time, 1 ), 0 ) AS char(2) ) -- number of seconds rounded ( if last character >=5, than + 1 )
AS datetime ) -- convert to datetime
, 114 ) -- and then convert to time
--------------------------------
If you use only
SELECT CAST( LEFT( @cYour_time, 4 ) / 60 AS char(2) ) + ':' + -- number of hours
CAST( LEFT( @cYour_time, 4 ) - ( LEFT( @cYour_time, 4 ) / 60 ) * 60 AS char(2) ) + ':' + -- number of minutes
CAST( ROUND( SUBSTRING( @cYour_time, 5, 2 ) + '.' + RIGHT( @cYour_time, 1 ), 0 ) AS char(2) ) -- number of seconds rounded ( if last character >=5, than + 1 )
you get the right value, but like this: '0 :12:59'
Zhavic
---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.