CODEWITHSUNDEEP
sql-serversql-server-2008date
daa daa
daa daa

Reputation: 141

How can i take time difference from minutes and hour?

I need to take hour and minutes diff but also to convert it as this type hh:mm

Example if i will have a time 12:41 and 11:31 i need to take in output 01:10

Here is what i'm trying to do

SELECT  DATEDIFF(MINUTE,datetime,getdate()) from Table_1

But i'm getting only minutes.

Upvotes: 1

Views: 125

Answers (2)

dfundako
dfundako

Reputation: 8314

You could grab the datediff in mins and add it to an arbitrary date and cast it to TIME. 

DECLARE @test1 TIME = '12:41'
DECLARE @test2 TIME = '11:31'

DECLARE @diff INT
SELECT @DIFF = DATEDIFF(MINUTE, @test2, @test1)
SELECT CAST(DATEADD(MINUTE, @diff, '1900-01-01') AS TIME)

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81960

Example

Select convert(varchar(5),DateAdd(MINUTE,DateDiff(MINUTE,'2018-06-28 11:31','2018-06-28 12:41'),0),108)

Returns

01:10

For your live test

Select convert(varchar(5),DateAdd(MINUTE,DateDiff(MINUTE,datetime,GetDate()),0),108)

Upvotes: 1

Related Questions