Re: SQL help

In article <to2bsbbotrqffe_at_corp.supernews.com>, "Axl" says...
>
>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)
>

or even better yet:

createdate between to_date( ?, 'mm/dd/rrrr' )

               and to_date( ?, 'mm/dd/rrrr' )+1-1/24/60/60

if you would like an index on createdate to be used.....

>"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
>>
>
>

--
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