-
Title
Errors when using Bind Variables with a Date Data type in the SQL editor window -
Description
When the user uses a bind variable for his date value he receives invalid results.
For example:
The user creates a table
CREATE TABLE TEST_D
(
TEST_DATE DATE
)
LOGGING
NOCACHE
NOPARALLEL;
INSERT INTO TEST_D ( TEST_DATE ) VALUES (
TO_Date( '08/28/2003 02:05:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST_D ( TEST_DATE ) VALUES (
TO_Date( '08/28/2003 03:10:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TEST_D ( TEST_DATE ) VALUES (
TO_Date( '08/29/2003 10:30:00 AM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
When the user runs the following statement (Bind Variables entered are 8/28/2003 3:00:00 AM and 8/28/2003 4:00:00 AM)
select count(*) from test_D where test_date > :in_date1 and test_date < :in_date2
The statement returns no rows.
If the user enters the date manually then the results are correct
select count(*) from test_D where test_date > to_date('20030828 030000','yyyymmdd hh24miss') and test_date < to_date('20030828 040000','yyyymmdd hh24miss')
--returns 1, correct
From the SQL Monitor you will notice that Toad will drop the hours thus causing the problems.
Timestamp: 13:12:07.948
select count(*) from test_D where Test_D.test_date > :in_date1 and
Test_D.test_date < :in_date2
in_date1 = '8/28/2003'
in_date2 = '8/28/2003' -
Resolution
WORKAROUND
Do not use bind variables for date data types. Hard code the date values in the select statement.
STATUS
This issue is fixed in Toad 8.0. This version can be downloaded at http://questsupportlink.quest.com/prod_upgrade_alert.asp.