Access SQL Time Formating?

[Jawo]

I have a database with all times recorded in seconds. However, I need to display the time in the conventional HH:MM:SS format. Stupid MSFT screwed with thier version of SQL and I cannot use the MYSQL or Oracle SQL formatting techniques.

I have tried using the FORMAT(FieldName, h:m:s) but it only converts numbers to the right of a decimal into that format. I can divide by 3600 to get hours, but thats jut messy. Additionally, I need to be able to show time above 24 hours (hence elapsed time).

 

[Crusty]

AFAIK the only way to do it IN a query is using FORMAT(time / <number of seconds per unit of time you are looking for>) a couple of times and then just use & to concat them all together.

 

[Jawo]

Originally posted by: Crusty
AFAIK the only way to do it IN a query is using FORMAT(time / <number of seconds per unit of time you are looking for>) a couple of times and then just use & to concat them all together.

I have thought about that, but its not pretty since I will be dealing 500,000+ seconds (150 hours+) entries.

If worse comes to worse, I'll just format it in Excel, right before I send it out, but I would like to do it in the query.

 

[KLin]

DECLARE @Seconds as float
declare @hours as float
declare @minutes as float

SET @seconds = 124500
set @Hours = Cast(@Seconds/3600 as float)
set @minutes = ((@hours - Cast(@hours as int))*3600)/60
set @Seconds = ((@minutes- Cast(@minutes as int))*60)
select
Right('000' + Cast(Cast(@hours as int) as varchar(5)), 3) + ':' +
Right('00' + Cast(Round(@minutes, 0) as varchar(5)), 2) + ':' +
Right('00' + Cast(Round(@seconds, 0) as varchar(5)), 2)


One way to do it in T-Sql. You can even put it into a function, pass the number of seconds, and have it return the elapsed time as a string.