Get diff of time Oracle

Question

I have the next idea :

SELECT TO_CHAR('14:00:00','HH24:MI:SS') - MIN(TO_CHAR(DATETIME,'HH24:MI:SS')) AS MINFECHA
  FROM ARCHIVO2
  WHERE DIA='LUNES';

I want to get the difference between the 2 fields that should be something like

00:46:00

Any comment will be appreciated.

You've removed a large chunk of important DATE data like day/month/year. If you're only using hours/minutes/seconds, you'll never be able to differentiate between 36 hours and 12 hours. — Mr. Llama, Oct 14 '15 at 16:07
Possible duplicate of [Calculate difference between 2 date / times in Oracle SQL](http://stackoverflow.com/questions/1096853/calculate-difference-between-2-date-times-in-oracle-sql) — Mr. Llama, Oct 14 '15 at 16:08
The fact is that for example an employee normally starts working at 14:00:00 but if the employee is late at work at 14:02:00 I want to be able to make the time diff that in this case it will be 00:02:00 (2 minutes late) — Jairo Ordaz Moreno, Oct 14 '15 at 16:09

score 1 · Answer 1 · answered Oct 14 '15 at 17:18

You can't add/subtract dates and times when they're in character strings. To accomplish what you're trying to do you need to convert the character strings to DATE values, perform the necessary calculations, and then convert the result back to a character string:

WITH DATE_DATA AS
  (SELECT DIA,
          DATETIME,
          TO_DATE(TO_CHAR(DATETIME, 'DD-MON-YYYY') || ' ' || '14:00:00', 'DD-MON-YYYY HH24:MI:SS') AS BASE_TIME
     FROM ARCHIVO2)
SELECT DIA,
       DATETIME,
       BASE_TIME,
       (DATETIME - BASE_TIME) * 1440 AS MINUTES_LATE
  FROM DATE_DATA;

SQLFiddle here

Best of luck.

Get diff of time Oracle

1 Answers1