Mayank Patel Mayank Patel - 20 days ago 5
SQL Question

How can i subtract one hh.mm format time(which is decimal) from another?

I have 4 records in an SQL database. Which has 2 colums hours1 and hours2


  • Record1 : hours1- 1.00,hours2- 1.00

  • Record2 : hours1- 9.00,hours2- 5.30

  • Record3 : hours1- 9.00,hours2- 9.00

  • Record4 : hours1- 3.00,hours2- 3.00



So here I got the sum of these colums as :

SUM of column1 : 22.00
SUM of column1 : 18.30


These are decimal but I consider it as time.

Now I want to subtract column2 sum from column1 sum i.e.

22.00 - 18.30


So I will get
3.7
which is wrong because I considered as a time.

I want final answer as : 3.30 (justification for requirement : so when I add this result to 18.30 (18.30+3.30 which is 21.60 = 22.00 hours as 60 minute = 1 hour) I got total 22.00).

Can anyone help me how to get
3.30
as result? I cant use TimeFormat.

Answer

As everyone said in the comments: conversion is needed, and it must be done before summing takes place.

Here is how:

var times1 = new decimal[] { 1.00m, 9.00m, 9.00m, 3.00m };
var times2 = new decimal[] { 1.00m, 5.30m, 9.00m, 3.00m };

var hours1 = ActualHours(times1).Sum(); // using System.Linq;
var hours2 = ActualHours(times2).Sum();
var diff = hours1 - hours2;

var result = "Actual: hours1 = " + hours1 +
                   ", hours2 = " + hours2 +
                   ", diff   = " + diff + "\r\n" +
            "Printed: hours1 = " + Print(hours1) +
                   ", hours2 = " + Print(hours2) +
                   ", diff   = " + Print(diff);

Methods used:

public IEnumerable<decimal> ActualHours(params decimal[] values)
{
    foreach (var v in values)
    {
        var t = Math.Truncate(v);
        yield return t + (v - t) / 0.6m;
    }
}

public string Print(decimal v)
{
    var t = Math.Truncate(v);
    return (t + (v - t) * 0.6m).ToString("0.00");
}

Output:

Actual: hours1 = 22.0, hours2 = 18.5, diff = 3.5
Printed: hours1 = 22.00, hours2 = 18.30, diff = 3.30