using dates and times in the where clause
519701Jun 22 2006 — edited Jun 22 2006I have a query:
select pat.patientlname || ', ' || pat.patientfname "PatientName",
ep.episodeid, ep.visitnumber, to_char(lnd.dischargedate, 'MM/DD/YYYY hh:mm PM') "DischargeDate"
from patient pat, subsystem sub, episode ep, lnd
where pat.patientid = sub.patientid
and sub.episodeid = ep.episodeid
and sub.subsystemid = lnd.subsystemid
and to_char(lnd.dischargedate, 'MM/DD/YYYY hh:mm PM') > '06/14/2006 12:00 AM'
and to_char(lnd.dischargedate, 'MM/DD/YYYY hh:mm PM') < '06/14/2006 12:00 PM'
order by lnd.dischargedate;
that needs to pull records with a discharge date between a certain time frame. The query runs, but in this example pulls all patients who were discharged at the given time for all years the system has been running -- every patient discharged on June 14th between midnight and noon, but for all years from '99 to '06. What's wrong with my syntax?
Thanks,
Thomas