Re: Help with date comparison

Rs176 (rs176_at_aol.com) wrote:
: I'm hoping someone can help solve a problem I'm having comparing dates. I
: have a table with a VARCHAR2 column holding dates in the format 'DD-Mon'.
: That is, one value is the string '17-Jul', another is '10-Dec', etc. (I
: must use VARCHAR2; much as I'd like to, I can't change it to DATE.)
 

: I would like to select all records from this table that have a date that
: is between a specific range. In the following attempt, I want to find all
: records with dates between 8-Dec and 10-Dec ('mydate' is the VARCHAR2
: column):
 

: select * from mytable
 

: where to_date(mydate, 'DD-MON') >= to_date('08-Dec', 'DD-Mon')
:
: and to_date(mydate, 'DD-MON') <= to_date('10-Dec', 'DD-Mon');
:
 

: However, this results in the following error:
 

: ORA-01839: date not valid for month specified
 

: I've tried many other approaches (I won't fill up the newsgroup by listing
: them), with no success. Can anyone recommend a solution?
 

: Thanks.
 

: ->Russ

The problem is not in your query but in your data. You have a record that contains a bad date. You need to find this offender, then fix it, then your query will work. P.S. you could try the BETWEEN syntax too.

Hope it helps
Randy :)

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I