on 06-22-2016 7:57 PM
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Kiran,
Which database are you using?
Didier
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
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:
In my previous answer you have to replace "Datetime Start" by "Createdate" and "Datetime End" by "Datedown".
Didier
User | Count |
---|---|
85 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.