Date range in Stored Procedure doesn't return all records?

[reecem]

I have created a Stored Procedure to select a range of records between certain dates in Informix 7.3 as below, but when inserted into Crystal Reports 8.5 and executed it returns 10 records instead of 54 records. Any ideas? Thanks in advance.

CREATE PROCEDURE invoice_valuation (sdate DATE, edate DATE)
RETURNING INTEGER, INTEGER, CHAR(16), CHAR(6), DECIMAL(8,3), DECIMAL(8,2), DATE,
DECIMAL(8,6), CHAR(3), INTEGER, DATE, INTEGER, DECIMAL(8,2), DECIMAL(8,3),
CHAR(30), CHAR(30), CHAR(3), CHAR(6), CHAR(30);
DEFINE p_orno INTEGER;
DEFINE p_pono INTEGER;
DEFINE p_item CHAR(16);
DEFINE p_cuno CHAR(6);
DEFINE p_pric DECIMAL(8,3);
DEFINE p_oqua DECIMAL(8,2);
DEFINE p_ddta DATE;
DEFINE p_rats DECIMAL(8,6);
DEFINE p_ccur CHAR(3);
DEFINE p_ssls INTEGER;
DEFINE p_invd DATE;
DEFINE p_invn INTEGER;
DEFINE p_dqua DECIMAL(8,2);
DEFINE p_amnt DECIMAL(8,3);
DEFINE p_nama CHAR(30);
DEFINE p_dsca CHAR(30);
DEFINE p_cuni CHAR(3);
DEFINE p_citg CHAR(6);
DEFINE p_idsc CHAR(30);
FOREACH
SELECT a.t_orno, a.t_pono, a.t_item, a.t_cuno, a.t_pric, a.t_oqua, a.t_ddta,
b.t_rats, b.t_ccur, c.t_ssls, c.t_invd, c.t_invn, c.t_dqua, c.t_amnt, d.t_nama,
e.t_dsca, e.t_cuni, e.t_citg, f.t_dsca
INTO p_orno, p_pono, p_item, p_cuno, p_pric, p_oqua, p_ddta, p_rats, p_ccur,
p_ssls, p_invd, p_invn, p_dqua, p_amnt, p_nama, p_dsca, p_cuni, p_citg, p_idsc
FROM ttdsls041100 a, ttdsls040100 b, ttdsls045100 c, ttccom010100 d,
ttiitm001100 e, ttcmcs023100 f
WHERE a.t_orno = b.t_orno
AND a.t_orno = c.t_orno
AND a.t_pono = c.t_pono
AND a.t_cuno = d.t_cuno
AND a.t_item = e.t_item
AND e.t_citg = f.t_citg
AND c.t_ssls > 6
AND c.t_invd >= sdate
AND c.t_invd <= edate
RETURN p_orno, p_pono, p_item, p_cuno, p_pric, p_oqua, p_ddta, p_rats, p_ccur,
p_ssls, p_invd, p_invn, p_dqua, p_amnt, p_nama, p_dsca, p_cuni, p_citg, p_idsc
WITH RESUME;
END FOREACH;
END PROCEDURE;

 

[olded]

Hi:

First, I have no experience with Crystal Reports. I'm assuming if you execute the SP from dbaccess/isql it retrieves the proper data? You don't mention one way or the other.

What date format are you sending to the SP? In Online, by default, the format is MM/DD/YYYY. This is controlled by the shell variable DBDATE. The default reads MDY4/

You can change this to something like MDY2/ which is MM/DD/YY.

Regards,


Ed