CODEWITHSUNDEEP
sqloracle-databasedateselectsubtraction
mtfalcon31
mtfalcon31

Reputation: 37

Oracle SQL - Subtracting Date types

I'm trying to project a column that subtracts an employee's start time from end time to get total hours worked.

Here's what I have:

select task_name "NAME", to_char(ses_start, 'DD-MON-RR') "DATE", to_char(ses_start,'HH:MI PM') "START", to_char(ses_end, 'HH:MI PM') "END",to_number(to_char(ses_end,'HH24:MI')) - to_number(to_char(ses_start,'HH24:MI')) "TOTAL"

The TOTAL column isn't working - is it even possible to do something like this? It worked up until I added that column, with output similar to:

TASK_NAME          DATE        START      END
----------------- ------------ ---------- -----------
create flyers     26-MAR-16    02:00 PM   04:30 PM
update website    28-MAR-16    11:00 AM   01:00 PM

So now I want a final column (preferably in hours):

TOTAL   
-----
2.5
2

or, at least in hours&minutes (this is what I was trying to do)...

TOTAL   
-------
02:30
02:00

Any suggestions?

Upvotes: 0

Views: 47

Answers (1)

Thierry
Thierry

Reputation: 5440

you can substract Date types in oracle, it gives you a decimal number which is the duration in days

select 
  task_name, 
  to_char(ses_start, 'DD-MON-RR') DATE,
  to_char(ses_start,'HH:MI PM') START, 
  to_char(ses_end, 'HH:MI PM') END,
  round((ses_end - ses_start) * 24, 2) DURATION_IN_HOUR
...

Upvotes: 3

Related Questions