Insert time of work done per day in Database and fetch total time taken to complete the task in HH:MM format

Question

1.12/5 (3 votes)

See more:

I need to get the start time and end time for a day from dropdownlists
per day entries will go to database in HH:MM format
like 1 hr and 15 min OR 1.15

After 1 week we need to get the total time of work done in 1 week in HH:MM format.

Total Time : HH:MM

Posted 13-Oct-14 21:33pm

Ashok Khatra.

Add a Solution

Comments

Sinisa Hajnal 14-Oct-14 4:18am

1.15 doesn't equal 1:15 if you're trying to save hours and minutes as numbers... 15 minutes is a quarter of the hour and thus would be 1.25h.

Nelek 14-Oct-14 5:35am

What have you tried?[^]
How to ask a question?[^]

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NaibedyaKar · Answer 1 · 2014-10-13T22:16:00

Hi Ashok, I think you can achieve this by having 2 fields in your table as StartTime and EndTime with datatype as time(7). Then you can save your data these fields.

To fetch the total time you might do as below

SQL

SELECT CONVERT(NUMERIC(18, 2), SUM(DATEDIFF(minute,StartTime,EndTime)) / 60 + (SUM(DATEDIFF(minute,StartTime,EndTime)) % 60) / 100.0)
FROM TaskEntry --Where TaskEntry is yourtable name

Sinisa Hajnal · Answer 2 · 2014-10-13T22:24:00

If you save your data as datetime, you can use DateDiff(mi, start date, end date) to get the difference between start time and end time in minutes per day. Then sum that across the date range (one week) to get total number of minutes.

Finally, divide it by 60 to get number of hours. This should be integer. THen do modulo of the number and 60 to get remainder of minutes.

SQL

SELECT total_time = SUM(minutes_worked), hours = FLOOR(SUM(minutes_worked) / 60),
minutes = SUM(minutes_worked) % 60
FROM
(
SELECT work_date, minutes_worked = DATEDIFF('mi', start, stop) FROM your_table
WHERE work_date BETWEEN first_day_of_the_week AND end_date_of_the_week
)

If this helps, please take time to accept the solution. Thank you.

Insert time of work done per day in Database and fetch total time taken to complete the task in HH:MM format

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0