I have two queries that I am trying to run. The user enters a startdate, a stopdate and a program. I then dynamically build the SQL statements are run them. On both queries, the field that I am using for the date range is defined as datetime. Query 1 works, query 2 blows out with a "Non-numeric character in datetime or interval" error.
What's the secret to querying Informix date fields? I haven't got much hair left, as I'm pulling it out on a daily basis?
Query 1:
SELECT MVOD.ord_area AS Program, V.med_rec_num AS PatientID, MVOD.patient_name, V.visit_id, V.admit_date, V.weight_kg, V.height_cm, MVOD.ord_room_bed, V.attending_doctor, P.birthdate, P.sex, MVOD.ord_order_num, MVOD.dos_generic_name, MVOD.dos_brand_name, MVOD.orh_verified, MVOD.ord_time_sch, MVOD.dos_admin_hour, MVOD.dos_dose, MVOD.dos_route, MVOD.dos_frequency, MVOD.dos_drug_name, MVOD.ord_time_act, MVOD.orh_admin_dose, MVOD.orh_disp_size, MVOD.orh_disp_unit, MVOD.ord_notes, MVOD.orh_start_date, MVOD.orh_stop_date, MVOD.ord_admin_by, A.description AS Allergies, C.description AS diagnosis, MVOD.dos_admin_instr, MVOD.dos_instruction, '05/25/2008' AS StartDate, '05/31/2008' AS EndDate FROM mar_vw_ord_dtl AS MVOD INNER JOIN visit AS V ON MVOD.visit_id = V.visit_id INNER JOIN patient AS P ON V.med_rec_num = P.med_rec_num LEFT JOIN allergy AS A ON P.med_rec_num = A.med_rec_num LEFT JOIN Conditions AS C ON V.visit_id = C.visit_id WHERE MVOD.ord_area ='COM' AND V.med_rec_num > '00009999' AND (MVOD.ord_time_sch Between '2008-05-25 00:00' And '2008-05-31 23:59') ORDER BY V.med_rec_num, MVOD.ord_order_num, MVOD.ord_time_act;
Query 2:
SELECT V.med_rec_num AS PatientID, MDAN.dan_entered_on AS NotesDate, MDAN.dan_entered_by AS EnteredBy, MDAN.dan_addl_comment AS Notes, MVOD.dos_brand_name, MVOD.ord_time_act, MVOD.ord_area AS Program FROM (mar_dose_addl_note MDAN INNER JOIN mar_vw_ord_dtl MVOD ON MDAN.dan_ord_id = MVOD.ord_id) INNER JOIN visit V ON MVOD.visit_id = V.visit_id where (MDAN.dan_entered_on between '2008-05-25 00:00' and '2008-05-31 23:59') and MVOD.ord_area='COM'
Start Free Trial
