Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 537
  • Last Modified:

Format SUM function

Greetings,

I have a database that has a field which contains seconds.  I am doing a Group By and using SUM on the seconds field.  I need to format the SUM results into HH:MM:SS.

Select Sum([Field1]),
Sum([Seconds]) AS MyDuration
Group By [Field3]
Where [Field4] = 'ABC'

MyDuration I need in HH:MM:SS

Thanks so much
0
jchauncey60
Asked:
jchauncey60
  • 2
2 Solutions
 
mbizupCommented:
Try this:


SELECT q.SumOfField1, Format(Int(q.[MyDuration]/3600),"00") & ":" & Format(Int((q.[MyDuration]-(Int(q.[MyDuration]/3600)*3600))/60),"00") & ":" & Format(((q.[MyDuration] Mod 60)),"00") AS ElapsedTime
FROM
(
Sum([Field1]) AS SumOFField1,
Sum([Seconds]) AS MyDuration
Group By [Field3] 
Where [Field4] = 'ABC'
) q

Open in new window

0
 
Dale FyeCommented:
If that value (Sum([Seconds]) is not going to exceed 24 hours, you could also do:

Format(Sum([Seconds])/86400, "hh:nn:ss")

dividing the sum by 86400 (the number of seconds in a day) will give you the decimal equivalent of a time value as stored in the database.
0
 
jchauncey60Author Commented:
Solution #2 works for this problem.  Solution #1 is the best for generic applications. Thanks for the quick response.
0
 
mbizupCommented:
Glad to help out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now