mtfalcon31 mtfalcon31 - 7 months ago 24
SQL Question

Oracle SQL Select Statement Calculations

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?

Answer

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
...
Comments