Need help converting time

[JVZ]

I have a string that is in the following format: MMMMSST
where M = min, S = Sec, and t = tenth of a sec.

so for example: 80, would be 8 sec...

I need help on how to convert that into HH:MM:SS format.

any ideas?

 

[zhavic]

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.