rdfield

If you are using Sybase or MS-SQL then you should be aware that TIMESTAMP has nothing to do with a time or date value, which would also cause your query to behave strangely (it's a binary counter that is guaranteed to change every time the row is updated).

Michael

Ignores as in "I get the same results whether I test this or not", or ignores as in "I can't get any results"

If the former, then my best guess is that it's doing an alphabetic comparison. So 11 March 2002 does lie between 1 July 2002 and 31 Dec 2002, since alphabetic sequencing runs 1, 11, 31. Worse, July to September includes March. OTOH, this doesn't gell with the example dates you've posted.

If you're getting no results, then my suspicion would be invalid SQL. Please post us the sample code, so we can have a look.

--
Tommy
Too stupid to live.
Too stubborn to die.

 my $statement = "SELECT id
FROM comments
WHERE comment_date BETWEEN '01-JUL-02' AND '01-DEC-02'";
[download]

This sql returns every id in the table "comments", no matter what comment_date is... I

If comment_date is indeed of type 'date' try something like:

my $statemenet = "select id
from comments
where comment_date >= to_date('01-jul-2002','dd-mon-yyyy')
and comment_date <= to_date('01-dec-2002',dd-mon-yyyy')";
[download]

Running your original code and this code on an Oracle database produces the same results. For a table containing the following dates:

CDATE
---------
01-JAN-02
01-FEB-02
01-MAR-02
01-APR-02
01-MAY-02
01-JUN-02
01-JUL-02
01-AUG-02
01-SEP-02
01-OCT-02
01-NOV-02
01-DEC-02
[download]

the above SQL statements both produce:

CDATE
---------
01-JUL-02
01-AUG-02
01-SEP-02
01-OCT-02
01-NOV-02
01-DEC-02
[download]

If the comment_date is converted to varchar, the following result is produced:

no rows selected
[download]

So that can't be the problem. You might want to run your query through an interactive session first and see what results it produces. My thought is that you are pulling the result set from a different query...

rdfield

By looking at the date format and syntax, my quess is you are using Oracle. I have found problems using the Oracle default date format with comparisons. What I tend to do in DBI code is set the date format with an alter session, usually right after the connect,

   my $dbh = DBI->connect();
      $dbh->do('alter session set nls_date_format = 'YYYYMMDD');
[download]

This sets the date format for the whole session. Then you can do compares with an easier format, select id from foo where date < 20001201; ..This also makes it easy to format a data file which may be read in for updates or comparisons to a db.

STH

Assuming that you are using a mySQL database, have a look at the "6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types"-section in the documentation. It explains all about these formats.

Just guessing, but I think you are looking at the display format of the field, whereas you would rather test the internal value.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

C.

my $statement = "SELECT id
FROM comments
WHERE comment_date BETWEEN '01-JUL-02' AND '01-DEC-2002";
[download]