Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL help
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('
TO_DATE('
TO_CHAR(TO_
-- 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 CorpReceived on Sat Aug 18 2001 - 14:09:01 CDT