cancel
Showing results for 
Search instead for 
Did you mean: 

I want to calculate the difference between 2 dates in hours and minutes

Former Member
0 Kudos

Hi Everyone,

I want to show the difference between 2 fields in hours and minutes.

I have tried using Date Diff function but it is showing only date in hours.

DateDiff("hour",Datedown,Createdate) 

Date down: 5/31/2016 2:58:33 PM and Create Date is 5/31/2016 6:00:44 i am getting result as 4 hours i need in hours and minutes like 4.23(hours.minutes).

I have tried using Date Diff function but it is showing only hours data.

DateDiff("hour",Datedown,Createdate) 

Is there any function where i can achieve the result?

Thanks,

Kiran

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello Kiran,

Please see below thread where I posted a similar solution but its at Webi report level.

Please see if you can convert this logic to handle at universe level else we can help you further.

Regards

Niraj

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Kiran,

Which database are you using?

Didier

Former Member
0 Kudos

Hi Didier,

I am using SQL Server Database.

Thanks,

Kiran

Former Member
0 Kudos

Hi Kiran,

Here is the SQL expression that you need to use to covert the date difference in hours and minutes:

CASE

WHEN DATEDIFF(minute,[Datetime Start],[Datetime End]) < DATEDIFF(hour,[Datetime Start],[Datetime End])*60 THEN

     CAST(CAST(DATEDIFF(hour,[Datetime Start],[Datetime End]) - 1 AS VARCHAR) + '.' + FORMAT(DATEDIFF(minute,[Datetime Start],[Datetime End]) - (DATEDIFF(hour,[Datetime Start],[Datetime End])*60) + 60, '00') AS FLOAT)

ELSE CAST(CAST(DATEDIFF(hour,[Datetime Start],[Datetime End])  AS VARCHAR) + '.' + FORMAT(DATEDIFF(minute,[Datetime Start],[Datetime End]) - (DATEDIFF(hour,[Datetime Start],[Datetime End])*60), '00') AS FLOAT)

END

In the expression I compute the difference in hours * 60 and in muntes and I compare the 2 results.

  • If the number of minutes computed with DateDiff(minute) is LOWER then I compute the number of hours with DateDiff(hour) and subtract 1 and I compute the difference of minutes between both expressions and add 60.
  • If the number of minutes computed with DateDiff(minute) is GREATER or EQUAL then I compute the number of hours with DateDiff(hour) and I compute the difference of minutes between both expressions.


I add a decimal point between the hours and minutes.

I convert all numeric expressions in VARCHAR using FORMAT to be sure to have the right number of minutes.

I convert the final result in FLOAT.


But using the FLOAT conversion does not reflect the number of minutes.

For instance if the result is 2 hours and 50 minutes, using the conversion the result will be 2.5 and that's not correct.

So I recommend to not use the numeric conversion and keep it in VARCHAR like this.


CASE

WHEN DATEDIFF(minute,[Datetime Start],[Datetime End]) < DATEDIFF(hour,[Datetime Start],[Datetime End])*60 THEN

     CAST(DATEDIFF(hour,[Datetime Start],[Datetime End]) - 1 AS VARCHAR) + '.' + FORMAT(DATEDIFF(minute,[Datetime Start],[Datetime End]) - (DATEDIFF(hour,[Datetime Start],[Datetime End])*60) + 60, '00')

ELSE CAST(DATEDIFF(hour,[Datetime Start],[Datetime End])  AS VARCHAR) + '.' + FORMAT(DATEDIFF(minute,[Datetime Start],[Datetime End]) - (DATEDIFF(hour,[Datetime Start],[Datetime End])*60), '00'))

END



Here is a screenshot of a sample:


Didier

Former Member
0 Kudos

Hello Kiran,

If you want to have a numeric result that represent the hours and the remaining minutes on a base 10, then here is the SQL syntax:

CASE

WHEN DATEDIFF(minute,[Createdate],[Datedown]) < DATEDIFF(hour,[Createdate],[Datedown])*60 THEN

     CAST(CAST(DATEDIFF(hour,[Createdate],[Datedown]) - 1 AS VARCHAR) +  FORMAT((CAST(DATEDIFF(minute,[Createdate],[Datedown]) - (DATEDIFF(hour,[Createdate],[Datedown])*60) + 60 AS FLOAT)) / 60, '#.00') AS FLOAT)

ELSE CAST(CAST(DATEDIFF(hour,[Createdate],[Datedown])  AS VARCHAR) +  FORMAT((CAST(DATEDIFF(minute,[Createdate],[Datedown]) - (DATEDIFF(hour,[Createdate],[Datedown])*60) AS FLOAT)) / 60, '#.00') AS FLOAT)

END

So for instance, using this expression:

  • 1 hours and 30 minutes will give 1.5
  • 2 hours and 15 minutes will give 1.25
  • etc.

In my previous answer you have to replace "Datetime Start" by "Createdate" and "Datetime End" by "Datedown".

Didier