Oracle Reports Date problem

From: <hummels_at_insightbb.com>
Date: 22 Aug 2006 11:35:49 -0700
Message-ID: <1156271749.490505.280070_at_p79g2000cwp.googlegroups.com>

[Quoted] [Quoted] I have a Query that works just fine in TOAD, but when put into Oracle Reports, it behaves differently. Table "clsd_assign" has a DATE column called "act_end". The following two SELECT statements bring back the same records in TOAD. But when put into Oracle Reports, I need to convert the DATE column to a DATE for it to work correctly (2nd query). The dates I was using were "18-AUG-06 00:00:00" and "18-AUG-06 23:59:59". Both brought back records with a date of 18-AUG-06. Once put into Oracle Reports, the first query did not bring back the records and the 2nd did. The first only worked when I expanded the dates to 17th through the 19th. The reason I need it to work with the first Query is that the Queries run long when I convert the act_end field. Any ideas why the difference?

SELECT *
FROM clsd_assign
WHERE assign_type = 'PP01'
AND act_end BETWEEN

	TO_DATE (:p_date1, 'DD-MON-YY HH24:MI:SS') AND
	TO_DATE (:p_date2, 'DD-MON-YY HH24:MI:SS'

SELECT *
FROM clsd_assign

WHERE assign_type = 'PP01'
AND to_date(act_end,'DD-MON-YY HH24:MI:SS') BETWEEN
	TO_DATE (:p_date1, 'DD-MON-YY HH24:MI:SS') AND
	TO_DATE (:p_date2, 'DD-MON-YY HH24:MI:SS'

Received on Tue Aug 22 2006 - 20:35:49 CEST