Hi All
I am using oracle 12c and have a query I need a little help on please.
select distinct l.location, l.sub_location,
to_char(r.e_date_start ,' DD-MON-YYYY HH24:MI:SS') e_date_start,
to_char(l.start_date,' DD-MON-YYYY HH24:MI:SS') l_start_date,
to_char(to_date(GREATEST(r.e_date_start , l.start_date),'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') start_date,
to_char(to_date(LEAST(NVL(r.e_date_end,SYSDATE),l.end_date) ,'DD-MON-YYYY HH24:MI:SS') ,'DD-MON-YYYY HH24:MI:SS') end_date
from location_instance l,rep_fitment_report_2 r
where l.start_date < nvl ( r.e_date_end, to_date ('31/12/2049', 'dd/mm/yyyy') )
and nvl(l.end_date,sysdate) >= r.e_date_start
and l.hla_serial = r.hla_serial
order by start_date
/
The resulting query gives me the following results....
The rep_fitment_report_2 has the following entry
So from my sql query when I carry out this part of the query i.e...
to_char(to_date(GREATEST(r.e_date_start , l.start_date),'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY HH24:MI:SS') start_date,
using GREATEST of r.e_date_start and l.start_date I need the time portion of the date to be brought back also as well as the correct date
ie for 2nd row 'start_date' should be bringing back '03-MAY-2018 08:23:24'
Similarly for 'End date' when I use the LEAST function in the query
Regards