1 2 3 4 5 6 7 8 9 10 11 12 13 14
| SELECT deb, fin,
TRUNC(( TO_NUMBER(SUBSTR(fin, 1, 2)) * 3600 + TO_NUMBER(SUBSTR(fin, 4, 2)) * 60 + TO_NUMBER(SUBSTR(fin, 7,2))
- TO_NUMBER(SUBSTR(deb, 1, 2)) * 3600 - TO_NUMBER(SUBSTR(deb, 4, 2)) * 60 - TO_NUMBER(SUBSTR(deb, 7,2))
) / 60) ||':'||
LPAD(MOD(TO_NUMBER(SUBSTR(fin, 1, 2)) * 3600 + TO_NUMBER(SUBSTR(fin, 4, 2)) * 60 + TO_NUMBER(SUBSTR(fin, 7,2))
- TO_NUMBER(SUBSTR(deb, 1, 2)) * 3600 - TO_NUMBER(SUBSTR(deb, 4, 2)) * 60 - TO_NUMBER(SUBSTR(deb, 7,2))
, 60), 2, '0')
AS diff
FROM (SELECT '05:10:00' AS deb, '06:20:00' AS fin FROM dual
UNION SELECT '05:10:10', '26:20:00' FROM dual)
DEB FIN DIFF
05:10:00 06:20:00 70:00
05:10:10 26:20:00 1269:50 |
Partager