CODEWITHSUNDEEP
sql-server-2008t-sql
jhowe
jhowe

Reputation: 10828

SQL Server time difference

Simple question, not so simple answer! If I have two dates in format arrival time, departure time respectively,

2014-10-16 10:29:42.000, 2014-10-16 12:04:48.000

how do I get the time difference between the two i.e. 'Loading Time' in hours and minutes? hh:mm?

Thanks!

Upvotes: 0

Views: 67

Answers (3)

Ullas
Ullas

Reputation: 11556

Try this,

First get the total seconds and divide it 3600 to get the hours.
Then take the reminder and divide it by 60 to get the minutes.
Convert those two integer values to VARCHAR as concatenate it.

Query

SELECT  CONVERT(VARCHAR(50), FLOOR(DATEDIFF(SECOND, '2014-10-16 10:29:42.000',
                                        '2014-10-16 12:04:48.000') / 3600))
    + ':' + CONVERT(VARCHAR(50), ( FLOOR(DATEDIFF(SECOND,
                                                  '2014-10-16 10:29:42.000',
                                                  '2014-10-16 12:04:48.000')
                                         % 3600) ) / 60)

Find demo here

Upvotes: 1

bummi
bummi

Reputation: 27377

Since the difference might be more then 24 hours you will need to display days in case off.

Declare @a Datetime
Declare @b Datetime
Set @b = '20141016 12:04:48.000'
Set @a= '20141016 10:29:42.000'
Select Isnull(Cast(NULLIF(DateDiff(dd,@a,@b),0) as Varchar(10)) + ' d ','') 
      + Convert (Varchar(20),Case when @a>@b then @a-@b else @b-@a end ,108)

01:35:06

Set @b = '20141018 12:04:48.000'
Set @a= '20141016 10:29:42.000'
Select Isnull(Cast(NULLIF(DateDiff(dd,@a,@b),0) as Varchar(10)) + ' d ','') 
     + Convert (Varchar(20),Case when @a>@b then @a-@b else @b-@a end ,108)

2 d 01:35:06

Upvotes: 1

Christian Phillips
Christian Phillips

Reputation: 18769

This would give you 95 mins in your case...

SELECT DATEDIFF(mi,'2014-10-16 10:29:42.000', '2014-10-16 12:04:48.000')

To convert it, you could use...

SELECT  CONVERT(VARCHAR(5), DATEADD(minute,
                                    DATEDIFF(mi, '2014-10-16 10:29:42.000',
                                             '2014-10-16 12:04:48.000'), 0), 114)

Upvotes: 2

Related Questions