Converting Integer to hh:mm:ss

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Converting Integer to hh:mm:ss

Author

Topic

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-07-22 : 11:12:32

I'm trying to display an integer field as HH:MM:SS but I can only get the MM:SS to show. The HH part is being displayed as zero and I know there are hours. I'll explain below.

The value for the Integer field (StaffTime) is 692238, which would translate to
192h:17m:18s. However I can only get my syntax to display it as
00h:17m:18s. Below is my syntax.

HoursLogged = convert(varchar(14), dateadd(second, sum(c.ti_stafftime),0), 108)

Can anybody shed a light as to what may be wrong with my code?

thx,

john

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-22 : 11:40:54

SQL Server's hh:mm:ss is on a 24 hour a day cycle, so hh will be between 0 and 23. You'll have
to write a UDF, something like this

 
/****** Object:  UserDefinedFunction [dbo].[fnSecondsToIntervals]    Script Date: 08/07/2007 13:13:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSecondsToHHMMSS] (@time int)
RETURNS varchar(40)

AS  
BEGIN


--DECLARE @time int
--SET @time = 392171--334396

DECLARE @Hour  int
 
DECLARE @min  int
DECLARE @secs int
DECLARE @retVal varchar(20)
 
SET @HOUR = @time/3600
SET @Min  =  (@time % 3600 )/60
SET @secs = @time %3600%60

SET @retval = REVERSE(CAST(@hour*10000 + @min * 100 + @secs as varchar(40)))
SET @retval =  REVERSE(LEFT(@retval,2) +':'+ SUBSTRING(@retval,3,2) +':' + SUBSTRING(@retval,5,40))

RETURN(@retval)
END

Jim

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-07-22 : 12:00:16

[code]
select
HoursLogged =
convert(varchar(20),datediff(hour,0,dateadd(second,sum(a.StaffTime),0)))+
right(convert(varchar(20),dateadd(second,sum(a.StaffTime),0),108),6)
from
(
-- Test Data
select StaffTime = 692238
) a

Results:

HoursLogged
--------------------------
192:17:18

(1 row(s) affected)
[/code]

CODO ERGO SUM

TommCatt
Starting Member

3 Posts

Posted - 2008-07-23 : 15:47:17

If you are going to maintain time as an integer, why not put it do it so the value representing 192:17:18 is 1921718? That puts it in human-readable form while maintaining sortability of the value and the conversion is simple:

declare @Time int;
set @Time = 1921718;

select @Time / 10000 as Hours, @Time % 10000 / 100 as Minutes, @Time % 100 as Seconds;

This provides +-24 years of range. You lose the ability to adjust the value by just adding seconds (without background logic, that is) but if you just convert to/from text ('192:17:18') then the ability to read the time directly from the value may be worth it.

--
Tomm
In theory there is no difference between theory and practice.
In practice, there is.

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-07-23 : 17:17:14

Thanks all three four your help. I don't know if I'd go with TommCatt's solution though. This would return three separate pieces of information that would need to be concatenated.

I went ahead and created the Function, but Michael's is another great solution.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 17:55:25

What's wrong with the suggestion I made at the other forum?

DECLARE        @Sample INT

SET        @Sample = 692238

SELECT        CAST(@Sample / 3600 AS VARCHAR(12)) + ':'
        + CONVERT(CHAR(8), DATEADD(SECOND, @Sample % 86400, '00:00:00'), 108)

E 12°55'05.25"
N 56°04'39.16"

TommCatt
Starting Member

3 Posts

Posted - 2008-07-23 : 18:04:33

quote:
Originally posted by latingntlman

I don't know if I'd go with TommCatt's solution though. This would return three separate pieces of information that would need to be concatenated.

I must be missing something. It seems to me that all the solutions return three separate pieces of information that need concatenation: hours, minutes and seconds.

--
Tomm
In theory there is no difference between theory and practice.
In practice, there is.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 18:07:25

MVJ and I concatenate the result into a string "hhh:mm:ss" wheras you provide a resultset with three columns and one record.

E 12°55'05.25"
N 56°04'39.16"

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-07-23 : 18:16:32

It returns this: 192:00:17:18
It should return 192:17:18

quote:
Originally posted by Peso

What's wrong with the suggestion I made at the other forum?
DECLARE @Sample INT

SET @Sample = 692238

SELECT CAST(@Sample / 3600 AS VARCHAR(12)) + ':'
+ CONVERT(CHAR(8), DATEADD(SECOND, @Sample % 86400, '00:00:00'), 108)

E 12°55'05.25"
N 56°04'39.16"

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-07-23 : 18:19:27

Not exactly; the syntax below

declare @Time int;
set @Time = 1921718;

select @Time / 10000 as Hours, @Time % 10000 / 100 as Minutes, @Time % 100 as Seconds;
Returs:
Hours Minutes Seconds
192 17 18
In three separate pieces. Of course all options require some form of concatenation but this piece I believe requires an extra step or two.

quote:
Originally posted by TommCatt

quote:
Originally posted by latingntlman

I don't know if I'd go with TommCatt's solution though. This would return three separate pieces of information that would need to be concatenated.

I must be missing something. It seems to me that all the solutions return three separate pieces of information that need concatenation: hours, minutes and seconds.

--
Tomm
In theory there is no difference between theory and practice.
In practice, there is.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 18:21:23

DECLARE @Sample INT

SET @Sample = 692238

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, @Sample % 86400, '00:00:00'), 108), 1, 2, CAST(@Sample / 3600 AS VARCHAR(12)))

E 12°55'05.25"
N 56°04'39.16"

latingntlman
Yak Posting Veteran

96 Posts

Posted - 2008-07-23 : 18:24:35

BAM!!! peso, you got it.

quote:
Originally posted by Peso

DECLARE @Sample INT

SET @Sample = 692238

SELECT STUFF(CONVERT(CHAR(8), DATEADD(SECOND, @Sample % 86400, '00:00:00'), 108), 1, 2, CAST(@Sample / 3600 AS VARCHAR(12)))

E 12°55'05.25"
N 56°04'39.16"

TommCatt
Starting Member

3 Posts

Posted - 2008-07-23 : 18:55:00

quote:
Originally posted by Peso

MVJ and I concatenate the result into a string "hhh:mm:ss" wheras you provide a resultset with three columns and one record.

Well, excuuuuuse me! I thought an illustration was sufficient, I didn't know you wanted a complete solution in final form.

declare @Time int;
set @Time = 1921718;

select Convert( varchar, @Time / 10000 ) + ':' 
     + Convert( varchar, @Time % 10000 / 100 ) 
     + ':' + Convert( varchar, @Time % 100 ) as FinalFrikkenResult;

Notice only arithmetical operations are used -- no DateAdd or other datetime functions.

Do you want the conversion from string to integer, also?

I ran across this in some forum somewhere and argued against it until I noticed that I could read the hours, minutes and seconds directly from the integer value and could still sort on the column with expected results. And if I needed to enter a value directly for, say, 11 hours, 7 minutes and 45 seconds, the value is 110745. No calculations needed. That sold it to me. I just thought that if you're going to be using an integer value to represent time, you might want to look into it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources