Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL help

Re: SQL help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 18 Aug 2001 12:09:01 -0700
Message-ID: <9lmegd01o2l@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 Sat Aug 18 2001 - 14:09:01 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US