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'
You need to be signed in and under a current maintenance contract to view premium knowledge articles.
© 2025 Quest Software Inc. ALL RIGHTS RESERVED. Terms of Use Privacy Cookie Preference Center