Re: SQL help

Thomas Kyte wrote:
>
> 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

along a similar vein but just easier to type

date_col between x and x+0.99999

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."