How to count the number of days between dates

Question

Im currently working on a project and the output im looking for, i need to count the number of days between an appointment and operation, currently i have this

Select Patient_FirstName ||' '|| Patient_surname "Patient Name", appointment_date, OPeration_date, datediff(Appointment_Date,Operation_Date) "Days till operation"
from PatientRecord p , Patient b, Appointment a, Operation o
where p.patient_ID = b.Patient_ID
and p.appointment_ID = a.appointment_ID
and p.operation_ID = o.OPeration_ID
order by Patient_Surname;

which is just returning an invalid identifier ,

when i round months it works fine, but i need days

Select Patient_FirstName ||' '|| Patient_surname "Patient Name", appointment_date, OPeration_date, Round (months_between(Appointment_Date,Operation_Date)) "Days till operation"
from PatientRecord p , Patient b, Appointment a, Operation o
where p.patient_ID = b.Patient_ID
and p.appointment_ID = a.appointment_ID
and p.operation_ID = o.OPeration_ID
order by Patient_Surname;

thats what i have for rounding months, is there something similar i can do for days?

Simply do `Appointment_Date - Operation_Date` to get the difference in days. — Wernfried Domscheit, Mar 20 '17 at 11:01
http://stackoverflow.com/questions/28406397/datediff-function-in-oracle — etsa, Mar 20 '17 at 11:03

Gordon Linoff · Answer 1 · 2017-03-20T11:56:15.147

0

First, Never use commas in the FROM clause. Always use proper, explicit JOIN syntax.

In Oracle, you can just subtract the dates:

Select Patient_FirstName ||' '|| Patient_surname as "Patient Name",
       appointment_date, Operation_date,
       trunc(Operation_Date - Appointment_Date) as "Days till operation"
from PatientRecord pr join
     Patient p
     on pr.patient_ID = p.Patient_ID join
     Appointment a
     on pr.appointment_ID = a.appointment_ID join
     Operation o
     on pr.operation_ID = o.Operation_ID
order by Patient_Surname;

edited Mar 20 '17 at 11:56

answered Mar 20 '17 at 11:02

Gordon Linoff

1,242,037
58
646
786

that trunc function worked thanks very much, in regards to the from clause, when i did the join syntax it didnt return the proper data but my original code did? – Henna Boom Mar 20 '17 at 11:09
`Patient p` should be `Patient b` (and the join condition needs fixing). Also, this does not consider when the appointment and operation have time components and are, for example, actually 2 days 21 hours apart but on the calendar this would be a difference of 3 days. – MT0 Mar 20 '17 at 11:17
Why can not use "comma" syntax ? Oracle before version 9 not support "join" syntax ... – Mike Mar 20 '17 at 14:16
@Mike . . . Oracle 9 supported explicit `JOIN`. Oracle 8 did not. – Gordon Linoff Mar 21 '17 at 02:49

score 0 · Answer 2 · answered Mar 20 '17 at 11:08

If the appointment_date is 2017-01-01 14:30:00 and the operation_date is 2017-01-04 09:30:00 then the difference is 2 days and 21 hours but you probably want this to be compared as the difference between 2017-01-01 and 2017-01-04 and displayed as 3 days (rather than 2) so:

Select Patient_FirstName ||' '|| Patient_surname AS "Patient Name",
       appointment_date,
       OPeration_date,
       TRUNC( Operation_Date ) - TRUNC ( Appointment_Date ) AS "Days till operation"
from   PatientRecord p
       INNER JOIN Patient b
       ON ( p.patient_ID = b.Patient_ID )
       INNER JOIN Appointment a
       ON ( p.appointment_ID = a.appointment_ID )
       INNER JOIN Operation o
       ON ( p.operation_ID = o.OPeration_ID )
order by Patient_Surname;

You could also use:

CEIL( Operation_Date - Appointment_Date ) AS "Days till operation"

the inner join function works, thanks again helped alot :) – Henna Boom Mar 20 '17 at 11:13 — Henna Boom, Mar 20 '17 at 11:13

score -2 · Answer 3 · answered Mar 20 '17 at 11:06

-2

Try this:

Select DateDiff(d,Appointment_Date,Operation_Date) as [Days till operation]

answered Mar 20 '17 at 11:06

Mardus Davel

77
8

1

Oracle does not have a `DATEDIFF` function. – MT0 Mar 20 '17 at 11:08

How to count the number of days between dates

3 Answers3