how to add floating numbers as hours give total hours with minutes and seconds

Question

i have simple problem i am having time in double format i.e 1.20 hr. 2.30 hr. 3.40 hr.

i want to add them as hours of time not as floating numbers ie to base 60 . for example

1.20+2.30+3.40=7.30 hrs -- correct

1.20+2.30+3.40=6.90 incorrect

i want it in both sql and c# , currently i am using custom made function fr both side . i want know any easiest way to do it.

if i am storing it in wrong format so please tell me how to do this right way, basically i am getting difference between two datetimes as float number . all possible solutions are welcome

Answer 1

I think there is no "fast" (read: built in) way. You will have to do it yourself.

• Split the numbers: the "whole number" part is the hour, the fraction (x 100) is the minutes
• add hours and minutes separately
• get "whole hours" from the minutes count and adjust both hour and minute count
• add the parts together: hours + minutes/100

Answer 2

double wholeHours = Math.Floor(timeVal);
TimeSpan realTime = new TimeSpan(wholeHours, (timeSpan - wholeHours) * 100 / 60;

You say "sql" which would suggest using the TIME datatype, however I'm guessing (since its most common with C#) that you mean SQLServer, which alas doesn't support the TIME datatype until 2008, so may not be available to you, in which case you'll have to keep using a float, and set it to the real number of hours with

FLOOR(timeVal) + (timeVal - FLOOR(timeVal)) * 100 / 60

Answer 3

Here's a way to do that in SQL. The ConvertedData subquery splits your time format into minutes and hours. The outer query sums them together, and converts them back to your float format:

select  sum(hours) + sum(minutes) / 60 as TotalHours
,       sum(minutes) % 60 as TotalMinutes
,       sum(hours) + sum(minutes) / 60 + sum(minutes) % 60.0 / 100 as YourDt
from    (
        select  cast(YourDt % 1 * 100 as int) as minutes
        ,       cast(YourDt as int) as hours
        from    (
                select  1.2 as YourDt
                union all
                select  2.3
                union all
                select  3.4
                ) SourceData
        ) ConvertedData

This prints:

TotalHours  TotalMinutes  YourDt
7           30            7.300000

Answer 4

If possible, try changing the format to something more sensible. Either total number of minutes or one field for hours and one for minutes. It will make your life easier.

Answer 5

Well, a .NET assembly can be called as stored proc within SQL server, hence your solution can be written in C#

I would use the TimeSpan object for this kind of calculation as shown already by Jon, with some added comfort:

public static TimeSpan AddMinutes(this TimeSpan span, double mins)
{
  return span.Add(TimeSpan.FromMinutes(mins));
}

public static string ToHourAndMins(this TimeSpan span)
{
  return string.Format("{0}:{1:00}", Math.Truncate(span.TotalHours), span.Minutes);
}

Then you can do:

var ts = TimeSpan.FromMinutes(20);
ts = ts.AddMinutes(45);
Console.WriteLine(ts.ToHourAndMins());

Answer 6

It's really a bad idea to calculate datetime in your way. Here is what you want:

    double original = 1.55d+2.9d+3.48d;
    double carry = (int)(original * 100) % 100 / 60;
    double result = original + carry - carry / 100 * 60;