on 06-24-2016 7:33 AM
Hi All,
I have a requirement to calculate time deduction calculation between 2 time fields defined as HH24:MIN format,
for example this is the calculation that should happen;
A-B = C
10:30 -02:30 = 08:30
What the formula for this? Should I use ADD_SECOND formula somehow
Thanks
Hi Dick,
You can use SECONDS_BETWEEN to calculate Hours and Minutes difference between two time fields.
Example:
SELECT SECONDS_BETWEEN ('02:30', '10:30') / 3600
As "HOURS_BETWEEN" FROM DUMMY
SELECT SECONDS_BETWEEN ('02:30', '10:30') / 60
As "MINUTES_BETWEEN" FROM DUMMY
SELECT SECONDS_BETWEEN ('10:30', '14:30') / 60
As "MINUTES_BETWEEN" FROM DUMMY
Regards,
Muthuram
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
This Query will give the required output:
SELECT
To_Int(SECONDS_BETWEEN ('10:30', '15:15') / 3600) || ' Hours ' ||
Mod ((SECONDS_BETWEEN ('10:30', '15:15') / 60),60) || ' Minutes '
As "Hours_BETWEEN"
FROM DUMMY
If you want the results in 4:45 format,
Please change the concatenation in SQL.
Regards,
Muthuram
Hi Dick Smiths,
There is a work around, that will do what you need.
Ex :
let us consider two time fields A and B in the format HH:MM (24 Hrs), then use this formula to find the difference between two fields.
STRING(ABS (HOUR(A) - HOUR(B))) + ':' + STRING(ABS (MINUTE(A) -MINUTE(B)))
This syntax will work in calculated columns of calculation view in hana.
If your scenario is different than this, let me know,
Hope this helps you,
Regards,
HarikrishnanS
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
10 | |
10 | |
10 | |
7 | |
6 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.