sum of time in sql server 2008 r2

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi guys,

i have problem in doing sum(time) in sql.

actually i getting the values, but there is some minor bug or i'm missunderstood.

i have tried this

DECLARE @tb TABLE (Opt time);
 
INSERT INTO @tb (Opt)
VALUES('23:59'), ('23:59');
 
SELECT
    Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],
    
    Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)) % 60)
    + ' minutes'
    As [Description]
FROM
    @tb
;

Result:  47:58

where as i'm looking for 48:00

but if i enter '24:00' instead of '23:59', its throwing an error.

what value should i enter to get 48:00

Can any one please help me.

Thanks

Posted 1-Dec-14 7:38am

abdul subhan mohammed

Add a Solution

Comments

Corporal Agarn 1-Dec-14 14:24pm

By-the-way BOL has for time data type: Range 00:00:00.0000000 through 23:59:59.9999999

2 solutions

Solution 2

The result you're getting is correct:

23 hours 59 minutes is 1439 minutes;
Two lots of 1439 minutes is 2878 minutes;
2878 minutes is 47 hours 58 minutes;

You can't store 24:00 in a time column - the range is 00:00 to 23:59:59.9999999:

time (Transact-SQL)[^]

Range: 00:00:00.0000000 through 23:59:59.9999999

This makes sense, since time represents a time of day, and there isn't a 24 o'clock. :)

If you're looking to store a duration, rather than a specific time, then you'll need a different data type. You could either use an int column and store the duration in minutes, or you could store two datetime2 columns to represent the start and end times of the event, and then calculate the difference.

Posted 1-Dec-14 8:24am

Richard Deeming

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)

/\jmot · Accepted Answer · 2014-12-01T07:49:00

try these..

SQL

SELECT
    Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],

    Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)+1) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)+1) % 60)
    + ' minutes'
    As [Description]
FROM
    time_table

Updated..It's bad idea,you better see @Richard Deeming solution

SQL

SELECT
    Sum(DateDiff(minute, 0, Opt)) As [TotalMinutes],
   case when Opt='23:59' then 
    Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)+1) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)+1) % 60)
    + ' minutes'
	else
	   Convert(varchar(5), Sum(DateDiff(minute, 0, Opt)) / 60)
    + ' hours '
    + Convert(char(2), Sum(DateDiff(minute, 0, Opt)) % 60)
    + ' minutes'
	end
    As [Description]

FROM
    time_table group by Opt