## 15 replies

Last post Mar 28, 2019 03:49 PM by PatriceSc

• StrangerMike

Member

483 Points

2238 Posts

### Sql Time difference

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

Thanks
• mgebhard

All-Star

49071 Points

21707 Posts

### Re: Sql Time difference

#### 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.

• limno

All-Star

123240 Points

10019 Posts

Moderator

### Re: Sql Time difference

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 *,
,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) /60
,'.',
,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

*/```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• eralper

Contributor

6081 Points

1445 Posts

### Re: Sql Time difference

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)

```

• PatriceSc

All-Star

47410 Points

17538 Posts

### Re: Sql Time difference

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.

• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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.....

I tried this

Thanks
• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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

Thanks
• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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?

Thanks
• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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?

Thanks
• limno

All-Star

123240 Points

10019 Posts

Moderator

### Re: Sql Time difference

To handle negative values:

```create table test (hhmmNum1 int,hhmmNum2 int)
insert into test values(4000,1520),(1520,4000)

Select *,
,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) /60
,'.',
,Dateadd(minute, hhmmNum1 % 100,   dateadd(hour, (hhmmNum1 / 100) % 100,'19000101')) ) %60)
) as [hh.mm]
from test

drop table test```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• limno

All-Star

123240 Points

10019 Posts

Moderator

### Re: Sql Time difference

#### StrangerMike

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 *,
,Dateadd(minute, (hhmmNum1*100) % 100,   dateadd(hour, ((hhmmNum1*100) / 100) % 100,'19000101')) ) /60
,'.',
,Dateadd(minute, (hhmmNum1*100) % 100,   dateadd(hour, ((hhmmNum1*100) / 100) % 100,'19000101')) ) %60)
) as [hh.mm]
from test

drop table test```

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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?

Thanks
• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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.

Thanks
• limno

All-Star

123240 Points

10019 Posts

Moderator

### Re: Sql Time difference

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.

Jingyang Li
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
• StrangerMike

Member

483 Points

2238 Posts

### Re: Sql Time difference

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!

Thanks
• PatriceSc

All-Star

47410 Points

17538 Posts