difference between two times

Question

Post reply

difference between two times

tanehome

Ten Centuries

Points: 1167
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: 1167 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: 246960 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;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101098 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Steve Collins SSCrazy Points: 2157 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: 1004343 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)