difference between two times

Question

Post reply

difference between two times

tanehome

Ten Centuries

Points: 1165
More actions
October 22, 2020 at 7:34 am

#3799985
hi all,
good day
I've write the below script to get the difference between two times
for this example the result should be 2:20 , I don't know why it gives 20:00
can anybody advise?
```
declare 
@hFrom nvarchar(2)='9',
@mFrom nvarchar(2)='0',
@hTo nvarchar(2)='11',
@mTo nvarchar(2)='20'

SELECT  CONVERT(CHAR(5), DATEADD(MINUTE, 60*(DATEDIFF(MINUTE,CAST(@hFrom +':'+@mFrom AS TIME), CAST(@hTo +':'+ @mTo AS TIME))), 0), 108) as TimeDiff
```

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

tanehome Ten Centuries Points: 1165 More actions · Answer 1

sorry, it seems that i need to sleep 🙂

i forget to divide by 60, it should be as below

SELECT  CONVERT(CHAR(5), DATEADD(MINUTE, 60*(DATEDIFF(MINUTE,CAST(@hFrom +':'+@mFrom AS TIME), CAST(@hTo +':'+ @mTo AS TIME)))/60, 0), 108) as TimeDiff

Phil Parkin SSC Guru Points: 246686 More actions · Answer 2

Using more appropriate data types:

DECLARE @hFrom TINYINT = 9
,       @mFrom TINYINT = 0
,       @hTo   TINYINT = 11
,       @mTo   TINYINT = 20;

WITH x
AS
 (SELECT TotalMins = DATEDIFF(MINUTE, TIMEFROMPARTS(@hFrom, @mFrom, 0, 0, 0), TIMEFROMPARTS(@hTo, @mTo, 0, 0, 0)))
SELECT TimeDiff = TIMEFROMPARTS(calcs.hrs, calcs.mins, 0, 0, 0)
FROM x
    CROSS APPLY
(SELECT hrs = x.TotalMins / 60, mins = x.TotalMins % 60) calcs;

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

The problem is that you multiplied the minutes difference by 60; just get rid of the "60*".


SELECT  CONVERT(CHAR(5), DATEADD(MINUTE, (DATEDIFF(MINUTE,CAST(@hFrom +':'+@mFrom AS TIME), CAST(@hTo +':'+ @mTo AS TIME))), 0), 108) as TimeDiff

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Steve Collins SSCrazy Points: 2068 More actions · Answer 4

Disclaimer: I'm not saying this is the correct way to do this. But it's compact and nice to look at imo. String integers convert implicitly and time differences are easily calculated. Something like this (as a novelty)

declare 
@hFrom nvarchar(2)='9',
@mFrom nvarchar(2)='0',
@hTo nvarchar(2)='11',
@mTo nvarchar(2)='20'

select d.mins/60 d_hrs, 
       d.mins%60 d_mins
from (select ((@hTo*60+@mTo)-(@hFrom*60+@mFrom)) mins) d;

d_hrsd_mins
220

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Just curious... where are the original "from" and "to" values coming from? I ask because, if we know that, we might be able to...

Strip a whole lot complexity from all of this and...
Make it so it will handle midnight crossings.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)