This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Re: use of BETWEEN for dates
From: Stacy Mader <Stacy.Mader@atnf.csiro.au>
Date: Fri, 08 Dec 2000 15:43:56 +1100
Stacy Mader wrote:
> Hi all,
>
> I'd like to retrieve all reports between two dates. Now this is
> relatively easy it the reports are in the same year,
> but I'm getting strange results when I search for reports spread over
> two years. For example, to retrieve
> reports between 01-DEC-99 and 20-JAN-00 (my date format is DD-MON-YY),
> I have the SQL as
> follows:
>
> SELECT report, date_reported FROM report_db WHERE date_reported BETWEEN
> '01-DEC-99' AND '20-JAN-00'
>
> With the above, I get a null return even though I know there are reports
> in this date range.
>
> Next I tried formatting date_reported like this:
>
> SELECT report , date_reported FROM report_db WHERE
> to_char(date_reported,'DD-MON-YY') BETWEEN '01-DEC-99' AND '02-JAN-00'
>
> The returned list all contain dates with DD = 01 or 02 (for example,
> 02-DEC-00, 01-MAY-00, 02-DEC-96)
>
> So I'm lost. has anyone got any bright ideas as to how I can do this?
>
Well I solved my problem. The SQL is:
SELECT report, date_reported from report_db WHERE
to_char(date_occurred,'DD-MON-YY') BETWEEN to_date('01-DEC-98','DD-MON-YY') AND
to_date('12-JAN-99','DD-MON-YY')
Regards,
Stacy.
===
Subject: Re: use of BETWEEN for dates
From: Tommy Wareing <p0070621@brookes.ac.uk>
Date: Fri, 8 Dec 2000 10:10:39 +0000
On Fri, Dec 08, 2000 at 03:43:56PM +1100, Stacy Mader wrote:
>
> Hi all,
>
> Well I solved my problem. The SQL is:
>
> SELECT report, date_reported from report_db WHERE
> to_char(date_occurred,'DD-MON-YY') BETWEEN to_date('01-DEC-98','DD-MON-YY') AND
> to_date('12-JAN-99','DD-MON-YY')
Arrrgh! No!
The problem you're having stems from comparing different data
types. In this case dates and strings. Because they're different,
Oracle has to convert some of the values into the other data type. But
it doesn't know which to convert.
In version 6 and earlier, it always used to convert everything to the
datatype on the left hand side (so, from your original query,
everything would become a date). But Oracle always said that this
behaviour was implementation defined, and in version 7 seem to have
reversed it. So in the case you originally posted, everything was
being converted to strings, and an alphabetical comparison was being
made.
So, originally, you mentioned dates between '01-DEC-99' and
'20-JAN-00'. For example '30-DEC-99' does *not* lie between those two
strings, because, alphabetically, they are sorted as '01-DEC-99',
'20-JAN-00', '30-DEC-99'.
And 'Apr-99' does not lie between 'Jan-99' and 'Dec-99' :)
You get the same problem with sorting numbers if they are converted to
strings:
1
10
11
20
21
etc.
Since you want to do date comparisons, the correct solution is to
convert all the values to dates:
SELECT report, date_reported
FROM report_db
WHERE date_occurred BETWEEN TO_DATE('01-DEC-98', 'DD-MON-YY')
AND TO_DATE('12-JAN-99', 'DD-MON-YY')
The problem with the code you've posted above is that you're
converting date_occurred into a string. So now, instead of your
original problem of comparing a date (converted into a string) against
two dates, you're comparint a string (explicitly converted from a
date, and implicitly converted back into a date) against two date
(both explicitly converted from strings).
So potentially, you're allowing the entire problem to resurface.
Oh, for a strongly typed language... :)
===
Subject: RE: use of BETWEEN for dates
From: Steve Sapovits <SapovitsS@globalsportsinc.com>
Date: Fri, 8 Dec 2000 05:28:15 -0500
You can compare dates as strings if you use a format not susceptible
to the string collating problems Tommy outlines. I often do it using
the format 'YYYYMMDD', or 'YYYYMMDDHH24MISS' for full date/times. Both
are okay in that regard. But comparing as dates may be clearer to most.
===
Subject: Re: use of BETWEEN for dates
From: Tommy Wareing <p0070621@brookes.ac.uk>
Date: Fri, 8 Dec 2000 11:08:18 +0000
On Fri, Dec 08, 2000 at 05:28:15AM -0500, Steve Sapovits wrote:
>
> You can compare dates as strings if you use a format not susceptible
> to the string collating problems Tommy outlines. I often do it using
> the format 'YYYYMMDD', or 'YYYYMMDDHH24MISS' for full date/times. Both
> are okay in that regard. But comparing as dates may be clearer to most.
There is that. But if you're actually doing the processing in the
Oracle database, you don't want to change the datatypes of the columns
if you can possibly avoid it, since this means that indexes on those
columns can't be used...
Conversly, if the comparison is happening in your perl code, you
probably do want to convert to numbers, using one of the formats Steve
suggests, since then you can use the numeric operators for
comparisons, rather than having to invoke slow date functions.
===
Subject: Re: use of BETWEEN for dates
From: Frank Ullrich <ful@admin.heise.de>
Date: Fri, 08 Dec 2000 12:00:30 +0100
Stacy,
you should set your database default date format to at least DD-MON-RR
(better to DD-MON-RRRR) and you will be out of trouble.
(see also below).
Regards,
Frank.
Stacy Mader wrote:
>
> Hi all,
>
> I'd like to retrieve all reports between two dates. Now this is
> relatively easy it the reports are in the same year,
> but I'm getting strange results when I search for reports spread over
> two years. For example, to retrieve
> reports between 01-DEC-99 and 20-JAN-00 (my date format is DD-MON-YY),
> I have the SQL as
> follows:
>
> SELECT report, date_reported FROM report_db WHERE date_reported BETWEEN
> '01-DEC-99' AND '20-JAN-00'
>
> With the above, I get a null return even though I know there are reports
> in this date range.
No! Obviously there are no reports between 20-JAN-2000 and 01-DEC-2099
(with your date format that's what you asked for!).
With format DD-MON-RR your string '01-DEC-99' will resolve to what you
mean (IF you run your query within the first half of this century).
Read the fine Oracle SQL manual about the various date formats and about
the NLS settings on client and server!
Alternative: use "BETWEEN to_date('01-DEC-99','DD-MON-RR') AND
to_date('20-JAN-00','DD-MON-RR')" in your where clause.
>
> Next I tried formatting date_reported like this:
>
> SELECT report , date_reported FROM report_db WHERE
> to_char(date_reported,'DD-MON-YY') BETWEEN '01-DEC-99' AND '02-JAN-00'
>
> The returned list all contain dates with DD = 01 or 02 (for example,
> 02-DEC-00, 01-MAY-00, 02-DEC-96)
>
> So I'm lost. has anyone got any bright ideas as to how I can do this?
===