CODEWITHSUNDEEP
sqlsql-serverdatabasedatetimetime
aparna rai
aparna rai

Reputation: 833

How to Find Time difference with hours, minutes and seconds (All Results) in SQL Server

How to Find Time difference with all conditions like

if difference only minutes

 49 mintus

if a difference in Hour 

 1 hour 20 minutes

i am trying 

SELECT DATEDIFF(MINUTE,'2018-08-09 10:16:49.000','2018-08-09 11:14:40.000') AS Diff

but this is only show in minutes

I have also tried this one

DECLARE @StartTime datetime = '2018-08-09 10:16:49.000',
        @EndTime datetime = '2018-08-09 12:44:05.000'
SELECT CAST(@EndTime - @StartTime as Time) As TimeDiffere

output : 02:27:16.0000000

but I want to 2hr 27mins 16Sec

Upvotes: 2

Views: 320

Answers (4)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

If you have a poor datetime functionality to get hh.mm.ss, then you can use apply :

select t.*, concat( (dtdiff / 3600), 'hr ', (dtdiff / 60) % 60, 'mins ', dtdiff % 60, 'sec')
from table t cross apply
     ( values (datediff(second, startdate, enddate)) 
     ) tt(dtdiff);

Upvotes: 0

akshay
akshay

Reputation: 777

 DECLARE @StartTime datetime = '2018-08-09 10:01:15.000',
        @EndTime datetime = '2018-08-09 12:50:00.000'
SELECT CONCAT((DATEDIFF(second,@StartTime,@EndTime )/60)/60,'hr ',(DATEDIFF(second,@StartTime,@EndTime)/60)%60,'mins ',DATEDIFF(second,@StartTime,@EndTime)%60,'Sec') 
As TimeDiffere

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

DECLARE @StartTime  datetime = '2018-08-09 12:16:49.000',
        @EndTime    datetime = '2018-08-09 12:44:05.000'

select  [hour] + [minute] + [second] as TimeDiffere
from    (
            select diff_sec = datediff(second, @StartTime, @EndTime)
        ) t
        cross apply
        (
            select  [hour] = isnull(convert(varchar(10), nullif(diff_sec / 60 / 60, 0)) 
                           + ' hours ', '')
        ) hr
        cross apply
        (
            select  [minute] = isnull(convert(varchar(10), nullif(diff_sec / 60 % 60, 0)) 
                             + ' mintues ', '')
        ) mn
        cross apply
        (
            select  [second] = isnull(convert(varchar(10), nullif(diff_sec % 60, 0)) 
                             + ' seconds', '')
        ) sc

/*  RESULT

27 mintues 16 seconds

*/

Upvotes: 0

D-Shih
D-Shih

Reputation: 46219

If your SQL-server version higher than 2012, you can try to sue format function.

CREATE TABLE T(
 StartTime datetime,
  EndTime datetime
);

insert into t values ('2018-08-09 10:16:49.000','2018-08-09 12:44:05.000')

Query 1:

SELECT format(EndTime - StartTime ,'hh\hr mm\min\s ss\Sec') As TimeDiffere
FROM T

Results:

|       TimeDiffere |
|-------------------|
| 02hr 27mins 16Sec |

NOTE

Backslash \ can escape keyword from the format like hr,min,s..., then display it as the original word.

Upvotes: 3

Related Questions