Hi, I have 2 columns that I need to subtract, each column contains a date field added to number field which gives datetime. Subtracting one column from the other is not
giving the correct information. The ONLEAVE_DATE and RETURN_DATE fields are date datatype. ONLEAVE_TIME and RETURN_TIME are number datatype =seconds
Oracle 8i
select mh.pt_code,
mh.adm_date ,
mh.disch_date,
mh.event_no,
ol.ONLEAVE_DATE+OL.ONLEAVE_TIME/86400,
ol.RETURN_DATE+OL.RETURN_TIME/86400,
trunc(ol.RETURN_DATE+OL.RETURN_TIME/86400-ol.onleave_DATE+OL.onleave_TIME/86400,1) as lol,
mh.adm_date
from k_yyyy ol,k_xxxx mh
where MH.ADM_DATE > '01/07/2012'
and ol.pt_code = mh.pt_code
and ol.event_no = mh.event_no
and ol.ONLEAVE_DATE is not null
and ol.rev_id is null
order by 1
Sample of data returned
| 13/03/2013 11:55:00 p.m. | 15/03/2013 4:00:00 p.m. | 3.6 |
| 14/03/2013 4:00:00 p.m. | 18/03/2013 12:00:00 p.m. | 5.1 |
| 27/02/2013 8:30:00 p.m. | 28/02/2013 12:00:00 p.m. | 2.3 |
| 28/02/2013 3:30:00 p.m. | 1/03/2013 4:00:00 p.m. | 2.3 |
| 16/02/2014 4:45:00 p.m. | 17/02/2014 11:45:00 a.m. | 2.1 |
| 11/02/2013 8:45:00 a.m. | 12/02/2013 5:00:00 p.m. | 2 |
| 18/02/2013 8:50:00 a.m. | 20/02/2013 5:00:00 p.m. | 3 |
| 24/01/2013 9:20:00 a.m. | 25/01/2013 5:00:00 p.m. | 2 |