locked
Sql Time difference RRS feed

  • Question

  • User-373571878 posted

    Hello,

    I have tried many attempts but I can't seem to find an answer.

    I have two time fields:    40 hours and 15 hours and 20 minutes.

    I just want to find the difference between the two.  I keep coming up with 24.80 instead of 24.40

    40.00 - 15.20 (in hours and minutes) looking for 24.40

    here is one of many attempts:

    select

    Convert(Decimal(6,2),(Round(cast(40.00 as int)+(((40.00-cast(40.00 as int))*.60)),2)) - 15.20) as Whole_DiffHrs

    Thanks

    Thursday, March 28, 2019 1:05 PM

Answers

  • User77042963 posted

    Thanks for all replies.

    Limno,  Thank you, I plugged some figures in and it brought back desired results. I am not sure if it works opposite 15.20 - 40.00 to get a negative time figure?
    Which in the app I am working on is a possibility.

    However it gives me something to work with.

    Here are the fields coming back during the select, perhaps there is something better to use to calculate the 24.40 result?

    If not, is there an inline approach I can use to remove the decimal so that I can use your suggestion as a field in the query (maybe a Replace?)

     

    create table test (hhmmNum1 decimal(4,2),hhmmNum2 decimal(4,2))
    insert into test values(40.00,15.20),(15.20,40.00)
    
    
    Select *,
    CONCAT(Datediff(minute,Dateadd(minute, (hhmmNum2*100) % 100,   dateadd(hour, ((hhmmNum2*100) / 100) % 100,'19000101')) 
    ,Dateadd(minute, (hhmmNum1*100) % 100,   dateadd(hour, ((hhmmNum1*100) / 100) % 100,'19000101')) ) /60
    ,'.',
    abs(Datediff(minute,Dateadd(minute, (hhmmNum2*100) % 100,   dateadd(hour, (hhmmNum2*100 / 100) % 100,'19000101')) 
    ,Dateadd(minute, (hhmmNum1*100) % 100,   dateadd(hour, ((hhmmNum1*100) / 100) % 100,'19000101')) ) %60)
    ) as [hh.mm]
     from test
    
    drop table test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 28, 2019 2:23 PM

All replies

  • User475983607 posted

    StrangerMike

    Hello,

    I have tried many attempts but I can't seem to find an answer.

    I have two time fields:    40 hours and 15 hours and 20 minutes.

    I just want to find the difference between the two.  I keep coming up with 24.80 instead of 24.40

    40.00 - 15.20 (in hours and minutes) looking for 24.40

    here is one of many attempts:

    select

    Convert(Decimal(6,2),(Round(cast(40.00 as int)+(((40.00-cast(40.00 as int))*.60)),2)) - 15.20) as Whole_DiffHrs

    Thanks

    I've explained the issue and solution on your similar thread.  You are confusing Base10 math with time math and significant figures.   First, convert the decimal to a seconds or milliseconds depending on the required accuracy.  Then do the math using, for example, seconds.  

    Your other thread is rounding second to the next minute.  Once you have the seconds bit of the time then you can make a rounding determination.

    Thursday, March 28, 2019 1:35 PM
  • User77042963 posted

    If you want to get your expected result, you need to define your input in a consistent way.

    Let's define your input as two 4 digits numbers: first two for hours and last two minutes as your scope. here is one example:

    create table test (hhmmNum1 int,hhmmNum2 int)
    insert into test values(4000,1520)
    
    
    Select *,
    CONCAT(Datediff(minute,Dateadd(minute, hhmmNum2 % 100,   dateadd(hour, (hhmmNum2 / 100) % 100,'19000101')) 
    ,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) /60
    ,'.',
    Datediff(minute,Dateadd(minute, hhmmNum2 % 100,   dateadd(hour, (hhmmNum2 / 100) % 100,'19000101')) 
    ,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) %60
    ) as [hh.mm]
     from test
    
    drop table test
    /*
    hhmmNum1	hhmmNum2	[hh.mm]
    14000	1520	24.40
    
    */

    Thursday, March 28, 2019 1:42 PM
  • User-595703101 posted

    Based on the script and function given at Calculate Time Operations in SQL Server, I prepared following SQL

    Basically, I convert all time expressions into seconds as unit, then convert them back to minutes, hours, etc

    declare @t1h int, @t2h int, @t2m int, @seconds int
    select @t1h = '40'
    select @t2h = '15'
    select @t2m = '20'
    
    set @seconds = (@t1h * 60 * 60) - (@t2h * 60 * 60 + @t2m * 60) 
    
    select  
      RIGHT('00' + CAST( (@seconds / 3600) as varchar(2)), 2) + ':' +
      RIGHT('00' + CAST( ((@seconds % 3600) / 60) as varchar(2)), 2) + ':' +
      RIGHT('00' + CAST( (@seconds % 60) as varchar(2)), 2)
    
    

    Thursday, March 28, 2019 1:43 PM
  • User753101303 posted

    You are using 15.20 for 15 hours 20 minutes but doesn't do any transformation on this value. Also use functions to solve a small problem and then move to the next rather than ending up in building a single complex and error prone expression.

    SELECT 40*60-(15*60+20) -- 1480 minutes
    SELECT 1480/60 -- 24 hours
    SELECT 1480%60 -- 40 minutes left

    Actually I would move away from this design to keep basic calculation on the db side and formatting the duration as I want on the client side.

    Edit: my understanding is that you never handle seconds but go down only to the minute.

    Thursday, March 28, 2019 2:01 PM
  • User-373571878 posted

    Thanks for all replies.

    Limno,  Thank you, I plugged some figures in and it brought back desired results. I am not sure if it works opposite 15.20 - 40.00 to get a negative time figure?
    Which in the app I am working on is a possibility.

    However it gives me something to work with.

    Here are the fields coming back during the select, perhaps there is something better to use to calculate the 24.40 result?

    If not, is there an inline approach I can use to remove the decimal so that I can use your suggestion as a field in the query (maybe a Replace?)

    WorkHours...Whole_WorkHours..TotHrs....Whole_TotHrs.....TotMTCHrs.....Whole_MTCHours...TotPayRollMinutes..PunchMinutes...DiffMins....DiffDec...Whole_DiffHrs
    ..40.00................................40.00............40.00...............40.00...................15.25...................15.20.................................2400..............................914...............1486............24.75.............24.80.....

    thanks for your time

    I tried this

    Thursday, March 28, 2019 2:08 PM
  • User-373571878 posted

    Thanks Mgebhard

    The user keeps changing their minds how they want the hours displayed.  Yes I am confusing them.  I've been back and fourth on this that I am losing site.

    I was hoping to bring back fields in all varieties.  And as I sent a result snippet, I am now wondering if the approach is wrong, perhaps it is easier to get what I need from the two minutes field?   Here is what I responded to Limno

    WorkHours...Whole_WorkHours..TotHrs....Whole_TotHrs.....TotMTCHrs.....Whole_MTCHours...TotPayRollMinutes..PunchMinutes...DiffMins....DiffDec...Whole_DiffHrs
    ..40.00..............................40.00............40.00.................40.00.......................15.25....................15.20................................2400...................914......................1486..........24.75..............24.80.....

    Thank you

    Thursday, March 28, 2019 2:13 PM
  • User-373571878 posted

    Thanks Patrice

    Yes only hours and minutes. is it easier to determine difference using decimal time values and then convert that to hours and minutes?

    Thursday, March 28, 2019 2:16 PM
  • User-373571878 posted

    Thanks eralper,

    I see what you are doing with the seconds but how to tighten this up as a field in a query and the seconds being a variable as they will change with each record?

    Thursday, March 28, 2019 2:18 PM
  • User77042963 posted

    To handle negative values:

    create table test (hhmmNum1 int,hhmmNum2 int)
    insert into test values(4000,1520),(1520,4000)
    
    
    Select *,
    CONCAT(Datediff(minute,Dateadd(minute, hhmmNum2 % 100,   dateadd(hour, (hhmmNum2 / 100) % 100,'19000101')) 
    ,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) /60
    ,'.',
    abs(Datediff(minute,Dateadd(minute, hhmmNum2 % 100,   dateadd(hour, (hhmmNum2 / 100) % 100,'19000101')) 
    ,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) %60)
    ) as [hh.mm]
     from test
    
    drop table test

    Thursday, March 28, 2019 2:19 PM
  • User77042963 posted

    Thanks for all replies.

    Limno,  Thank you, I plugged some figures in and it brought back desired results. I am not sure if it works opposite 15.20 - 40.00 to get a negative time figure?
    Which in the app I am working on is a possibility.

    However it gives me something to work with.

    Here are the fields coming back during the select, perhaps there is something better to use to calculate the 24.40 result?

    If not, is there an inline approach I can use to remove the decimal so that I can use your suggestion as a field in the query (maybe a Replace?)

     

    create table test (hhmmNum1 decimal(4,2),hhmmNum2 decimal(4,2))
    insert into test values(40.00,15.20),(15.20,40.00)
    
    
    Select *,
    CONCAT(Datediff(minute,Dateadd(minute, (hhmmNum2*100) % 100,   dateadd(hour, ((hhmmNum2*100) / 100) % 100,'19000101')) 
    ,Dateadd(minute, (hhmmNum1*100) % 100,   dateadd(hour, ((hhmmNum1*100) / 100) % 100,'19000101')) ) /60
    ,'.',
    abs(Datediff(minute,Dateadd(minute, (hhmmNum2*100) % 100,   dateadd(hour, (hhmmNum2*100 / 100) % 100,'19000101')) 
    ,Dateadd(minute, (hhmmNum1*100) % 100,   dateadd(hour, ((hhmmNum1*100) / 100) % 100,'19000101')) ) %60)
    ) as [hh.mm]
     from test
    
    drop table test

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 28, 2019 2:23 PM
  • User-373571878 posted

    Hi limno,

    I found if I subtract the two minutes fields 2400 - 914 and run through this conversion, I get very close:

    SELECT (2400 - 914) / 60 + ((2400 - 914) % 60) / 100.0

    = 24.46

    But the 24.46 minutes should be 24.40.....

    Perhaps the answer lies in there somewhere? 

    Thursday, March 28, 2019 2:29 PM
  • User-373571878 posted

    Limno,

    Yes this worked!  Thank you for your time and patience. I will try and plug this into query.

    Just wondering is using the difference in minutes some how possibly a simpler way?

    Either way, thank you.

    Thursday, March 28, 2019 2:41 PM
  • User77042963 posted

    It is 24:46 (hh:mm). You should keep your stuff consistent otherwise it will confuse yourself before others get confused.

    Why do you struggle with digit number with your time value? They are different thing in my view.

    Thursday, March 28, 2019 2:42 PM
  • User-373571878 posted

    Thanks

    Time data is coming from different sources.  Some report time like 7.50  ( 7 and a half hours) and other 7.30 (for 7 and a half hours).

    It gets maddening trying to keep it straight.

    I plugged in your solution and it worked.  Until the next time challenge!

    Thursday, March 28, 2019 2:54 PM
  • User753101303 posted

    And those values are "injected" inside your app without any possible action or you could "normalize" those values ? If you currently store both format in your db, the meaning depends on the table/column where it is found or maybe on some other columns which tells from which source it comes from ?

    If I can I would really "normalize" those values storing them likely as minutes (you can then use SUM,AVG, + - etc.. directly) and I would do the formatting when I'm about to show this value to a user.

    Thursday, March 28, 2019 3:49 PM