How to display Hours and Minutes between two dates

Question

I am having a table consists of to datetime columns "StartTime" and "CompleteTime". Initially completeTime column will be NULL untill the process is completed. And now my requirement is to display hours and minutes as shown Below

Output: Ex: 2:01 Hr (This Means "2" represents hours and "01" represents minutes)

I Tried as below:

Declare @StartDate dateTime = '2016-03-31 04:59:11.253'
Declare @EndDate dateTime = GETUTCDATE()

SELECT REPLACE(CONVERT(VARCHAR,CAST(DATEDIFF(second, @StartDate, 
ISNULL(GETUTCDATE(),@EndDate)) / 36000.0 AS DECIMAL(9,2))) + ' hr','.',':')

Output:

0:05 hr

Required Output:

0:32 hr

Note: Hi while giving negative marking check my query once. I already referred some link's related to this but it won't work.

http://stackoverflow.com/questions/13577898/sql-time-difference-between-two-dates-result-in-hhmmss — mohan111, Mar 31 '16 at 05:36

score 1 · Answer 1 · answered Mar 31 '16 at 05:48

1

try this (MS SQL query) -

Declare @StartDate dateTime = '2016-03-31 04:59:11.253'
Declare @EndDate dateTime = GETUTCDATE()

SELECT CONVERT(varchar(5), 
   DATEADD(minute, DATEDIFF(minute, @StartDate, @EndDate), 0), 114) + ' hr'

Result - 00:47 hr

answered Mar 31 '16 at 05:48

Vinit

2,540
1
15
22

Diff more than 24 hour will not handle this – Abdul Rasheed Mar 31 '16 at 06:15

Abdul Rasheed · Accepted Answer · 2016-03-31T05:59:43.223

1

Try this

DECLARE @STARTDATE DATETIME = '2016-03-31 04:59:11.253'
DECLARE @ENDDATE DATETIME = GETUTCDATE()

SELECT CONVERT(VARCHAR(10),DATEDIFF(MINUTE, @STARTDATE, @ENDDATE)/60)+':'+CONVERT(VARCHAR(10),DATEDIFF(MINUTE, @STARTDATE, @ENDDATE)%60)+' hr' AS DIFF

Result:

Diff
0:52 hr

Diff more than 24 hour also will handle this

72:56 hr

edited Mar 31 '16 at 05:59

answered Mar 31 '16 at 05:52

Abdul Rasheed

6,486
4
32
48

Hart CO · Answer 3 · 2016-03-31T05:58:15.903

Declare @StartDate dateTime = '2016-03-31 04:59:11.253'

SELECT CONVERT(varchar(10),DATEADD(minute, DATEDIFF(minute, @StartDate,GETUTCDATE()), 0), 114) + ' Hr'

Returns: '0:43 Hr'

Your ISNULL(GETUTCDATE(),@EndDate) isn't doing anything since GETUTCDATE() will never be NULL, and you were dividing by 36000 instead of 3600, but the primary issue with your query is that you were putting a decimal value into a sexagesimal container (60 minutes/hour). Ie: 90 seconds should be 1:30 instead of :9

Edit: Mixup in my initial query, the DATEADD() method is cleaner.

How to display Hours and Minutes between two dates

3 Answers3