CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
Dinesh Reddy Alla
Dinesh Reddy Alla

Reputation: 1717

How to display Hours and Minutes between two dates

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.

Upvotes: 4

Views: 838

Answers (3)

Abdul Rasheed
Abdul Rasheed

Reputation: 6729

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

Upvotes: 1

Hart CO
Hart CO

Reputation: 34784

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.

Upvotes: 0

Vinit
Vinit

Reputation: 2607

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

Upvotes: 1

Related Questions