displaying in hh:mm:ss format - SQL Server Forums

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

SQL Server Development (2000)

displaying in hh:mm:ss format

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-03 : 07:55:20

Viggy writes "i want to display a float field in hh:mm:ss format. i tried to use Convert function but it shows the value as 00:00:00
please help me out ASAP"

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 08:27:52

declare @a float

set @a = 12345.678

select right('00' + cast(datepart(hh, cast(@a as datetime)) as varchar(2)), 2) + ':' + right('00' + cast(datepart(mm, cast(@a as datetime)) as varchar(2)), 2) + ':' + right('00' + cast(datepart(ss, cast(@a as datetime)) as varchar(2)), 2)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-03 : 08:59:55

Ditch, I think yours is not the correct solution

declare @a float
set @a = 12345.678
Select cast(@a as datetime)

set @a = 1234.678
Select cast(@a as datetime)

set @a = 123.678
Select cast(@a as datetime)

All Time parts seem to return same result

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 09:06:30

Thats cos the time is stored in the decimal part and you used the same decimal value each time.
try it with different decimal values as well

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-03 : 09:19:56

Well

Am I correct here?

declare @sec int
set @sec=12345.678

select
convert(varchar(5),@sec/3600)+':'+
convert(varchar(5),@sec%3600/60)+':'+
convert(varchar(5),(@sec%60))

But I would advise the questioner to do it in the Front End Application than in SQL Server

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 09:26:50

you must also rememver that the milliseconds are also stored in the decimal part

Duane.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-03 : 09:31:56

A more practical use of this can be found in Bretts profile http://www.sqlteam.com/forums/pop_profile.asp?mode=display&id=7198
see how he dynamically displays his age :)

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-03 : 10:17:54

Well
I thought that float value has values in seconds

Madhivanan

Failing to plan is Planning to fail

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-10-03 : 10:35:00

All numeric types are CAST or CONVERTed to datetime by treating the value as an offset in days from the datetime origin (19000101 00:00:00.000). Fractional values will be treated as fractions of days.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-03 : 12:05:40

I suspect that Viggy has made up his own "format" for storing times as floats, and he should probably let us know what that specification is. For all we know, it could be the # of milliseconds since 12:00:00AM or in the format of hhmmss or something like that.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-10-03 : 22:31:20

or he can probably reverse what he did to come up with the float value in the first place

--------------------
keeping it simple...

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-10-04 : 08:40:24

Is this a case of
1) Store your data in the proper type (DateTime, Float, Varchar)
OR
2) Display = Client side problem
OR
3) 1 and 2

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources