stored proc output problem
Hi,
I have created a stored procedure to view transaction records in some tables. And here it is:
CREATE DEFINER=`root`@`%` PROCEDURE `sp_DTOR01`(v_facility_no VARCHAR(12),v_begin_date DATETIME,v_end_date DATETIME, v_sna_users VARCHAR(20))
SELECT
A.facilityno,
A.ordt,
A.orno,
IFNULL(A.oramt,0.00) AS oramt,
UPPER(CONCAT(
IFNULL(A.clientLname,''), SPACE(1),
IFNULL(A.clientFName,''), SPACE(1),
IFNULL(A.clientMName,'')
)) name_of_client,
UPPER(B.facilityname) AS facilityname,
v_sna_users AS cur_user_id,
CONVERT(DATE_FORMAT(IFNULL(v_begin_date,NULL),'%m/%d/%Y') USING UTF8) AS start_date,
CONVERT(DATE_FORMAT(IFNULL(v_end_date, NULL),'%m/%d/%Y') USING UTF8) AS end_date,
B.adr AS facilityaddress,
B.telNo AS facilitytelno
FROM DTO_CLIENT_DTL A
LEFT JOIN REF_FACILITY B ON A.facilityNo = B.facilityNo
WHERE
IFNULL(A.facilityNo,'') = IFNULL(v_facility_no,IFNULL(A.facilityNo,'')) AND
DATE_FORMAT(A.orDt,'%m/%d/%Y') BETWEEN
DATE_FORMAT(IFNULL(v_begin_date,A.orDt),'%m/%d/%Y') AND
DATE_FORMAT(IFNULL(v_end_date, A.orDt),'%m/%d/%Y') $$
to call the stored proc: call sp_DTOR01('','2011-01-01','2011-12-31','');
the problem is it displays records even not in the date range.