Re: dates and 2001

Frank Greene <74200.427_at_CompuServe.COM> writes:

>How do you write a BETWEEN retrieval statement that works for
>dates which span the century mark? I have tried
 

>SELECT the_date_field
>FROM the_table
>WHERE TO_CHAR(the_date_field, 'mm/dd/yyyy')
>BETWEEN '06/12/1994' AND '06/12/2003'
>;

I think this would do a _lexical_ comparison rather than a numerical comparison. Changing the order of the date conversion would stand a better chance of working:

SELECT list FROM table
WHERE to_char(date_field,'yyyy/mm/dd')
between '1994/06/12' and '2003/06/12';

On top of that, this is somewhat backwards. It forces Oracle to convert _every_ non-null date_field to a character field and may run rather slow, because it will do a full table scan even if date_field is an indexed field.

The following should work better:

SELECT list FROM table
WHERE date_field between to_date('06/12/1994','mm/dd/yyyy') and to_date('06/12/2003','mm/dd/yyyy');

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)