Thanks, that led me down the right path. I found that when doing the
comparison, I had to truncate the date column to eliminate the time.
to_date(createddate,'MM/DD/YYYY') = trunc(CREATED)
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:9lmegd01o2l_at_drn.newsguy.com...
> In article <tnt0o3auqdu92_at_corp.supernews.com>, "Axl" says...
> >
> >I'm not sure if this is the right place for this question but here goes.
I
> >am writing a java servlet that creates a SQL statement based on some text
> >entered from a web page and executes it against a few Oracle tables. My
> >problem is the syntax to query against a date column. The date is stored
in
> >Oracle one way, but my users will enter the date themselves in a
different
> >format. For example, they could enter 5/4/2001 as the date to query
> >against. I thought I could use to_char(CreatedDate, 'fmMM/DD/YYYY'), but
> >that doesn't work. If they enter 05/04/2001 it will work, but I can't
count
> >on them to enter it correctly with 2 digit months and days. I could
write
> >some javascript code on the front end to correct the date, but I know
there
> >must be an easier way to do this. Any ideas?
> >
> >Thanks,
> >Axl
> >
> >
>
>
> You should convert THEIR input string into a date. eg:
>
> select * from t where createdDate = to_date( ?, 'mm/dd/rrrr' );
>
> Don't converted the stored date into a string to compare -- convert the
user
> input string to a date and compare.
>
> That to_date(?, 'mm/dd/rrrr' ) will accept 05/04/2001 or 5/4/2001 or even
5/4/01
>
> ops$tkyte_at_ORA8I.WORLD> select to_date('05/04/2001','mm/dd/rrrr' ) from
dual;
>
> TO_DATE('
> ---------
> 04-MAY-01
>
> ops$tkyte_at_ORA8I.WORLD> c'05/04'5/4'
> 1* select to_date('5/4/2001','mm/dd/rrrr' ) from dual
> ops$tkyte_at_ORA8I.WORLD> /
>
> TO_DATE('
> ---------
> 04-MAY-01
>
> ops$tkyte_at_ORA8I.WORLD> c/2001/01/
> 1* select to_date('5/4/01','mm/dd/rrrr' ) from dual
> ops$tkyte_at_ORA8I.WORLD> select to_char(to_date('5/4/01','mm/dd/rrrr' ),
> 'dd-mon-yyyy' ) from dual;
>
> TO_CHAR(TO_
> -----------
> 04-may-2001
>
> --
> Thomas Kyte (tkyte@us.oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Mon Aug 20 2001 - 10:40:06 CDT
