convert decimal to time 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 2005 Forums

Analysis Server and Reporting Services (2005)

convert decimal to time HH:MM:SS

Author

Topic

rlfassina
Starting Member

3 Posts

Posted - 2010-07-18 : 15:35:20

how can i convert 0.00833333333333333 to something similar to HH:MM:SS

0.0083....represents the difference between (EndDateTime - StartDateTime), i needed it as a decimal value so i can sum all the records and get the total time (see example)

start end duration
01/01/2010 8:00:00 01/01/2010 15:00:00 7:00:00
01/02/2010 23:00:00 01/03/2010 02:00:00 3:00:00
01/03/2010 20:00:00 01/14/2010 21:00:00 25:00:00

TOTAL 35:00:00

Thank you.

rlfassina
Starting Member

3 Posts

Posted - 2010-07-18 : 20:06:08

the proc returns 0.224166666666667 if i multiply it by 3600 i get 807 seconds

i used this formula to calculate MM:SS and i get 13:07 (which is correct)

=FLOOR(Fields!TimeElapsed.Value*3600/60) & ":" & RIGHT("0" & (Fields!TimeElapsed.Value*3600 MOD 60), 2)

I'd like to have HH:MM:SS or HH:MM but i can't figure out how to get it right. Any idea?

rlfassina
Starting Member

3 Posts

Posted - 2010-07-19 : 12:14:23

GOT IT!

=Floor(Fields!Time.Value*3600 / 3600) & ":"& RIGHT("0" &Floor(Fields!Time.Value *3600/ 60) - Floor(Fields!Time.Value*3600 / 3600) * 60, 2) & ":"& RIGHT("0" & Fields!Time.Value*3600 - Floor(Fields!Time.Value*3600 / 60) * 60, 2)

and this is for the sum

=Floor(Sum(Fields!Time.Value*3600) / 3600) & ":"& RIGHT("0" & Floor(Sum(Fields!Time.Value *3600)/ 60) - Floor(Sum(Fields!Time.Value*3600) / 3600) * 60, 2) & ":"& RIGHT("0" & Sum(Fields!Time.Value*3600) - Floor(Sum(Fields!Time.Value*3600) / 60) * 60, 2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources