CODEWITHSUNDEEP
ms-accesstimeformatting
Daniel Corriveau
Daniel Corriveau

Reputation: 3

SQL time format hours minutes in access

I want to create a query in Access that will sum up the total of time giving per project.

I have a database that look like:

 Time  |  Project
------------------
 1:00  |   1455
 2:30  |   1666
 0.50  |   1455
 0.45  |   1455

I want to know how many hours were given to all project.

So far I have created a query that return the total of hours per project, which is good.

However, the format of the time is odd.For instance a project that should return 17:10 hours return this 0.715277777777778

I am trying to format the field Time so the total return a normal looking format like 17:10 hours instead of all those decimals.

This is the SQL I have right now

SELECT Sum(Dan.Time) AS SumOfTime, Dan.Project
FROM Dan
GROUP BY Dan.Project
ORDER BY Dan.Project;

Can anyone help?

Upvotes: 0

Views: 3149

Answers (2)

4dmonster
4dmonster

Reputation: 3031

You can use format function:

Format(Sum(Dan.Time),"hh:nn") AS SumOfTime

P.S. DateTime in ms Access are in fact double where 1 is one day, and 1/24 is one hour and so on.

Upvotes: 1

AbcAeffchen
AbcAeffchen

Reputation: 15007

I'm not working with ms access, but maybe this helps you.

All times should be saved in the same type. Maybe you should format the times before storing them in the database.

If it is not possible, you can try this

SELECT 
  Int(Sum(Dan.Time) * 24) & ":" & (Int((Sum(Dan.Time) * 24 - Int(Sum(Dan.Time) * 24)) * 60) + 1) 
  AS TimeStr, 
  Dan.Project 
FROM 
  Dan 
GROUP BY 
  Dan.Project 
ORDER BY 
  Dan.Project;

It computs the hours and the minutes from the decimal number and concatenates the numbers as HH:MM. Maybe there is also a better build in way in ms access.

Upvotes: 0

Related Questions