Re: DBI and TIMESTAMP

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.

Comment on Re: DBI and TIMESTAMP

Replies are listed 'Best First'.
Re: Re: DBI and TIMESTAMP by Anonymous Monk on Feb 03, 2003 at 13:51 UTC
`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	[reply] [d/l]
Re: Re: Re: DBI and TIMESTAMP by rdfield (Priest) on Feb 03, 2003 at 16:51 UTC
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	[reply] [d/l] [select]
Re: Re: Re: DBI and TIMESTAMP by sth (Priest) on Feb 03, 2003 at 19:04 UTC
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	[reply] [d/l]