Re: Dates and Oracle

You want your SQL statement to look like:
> select * from table1 where to_char(the_date, 'dd-mm-yyyy') = '01-07-1998'

Otherwise you are saying, give me all the rows where all my stored date (the_date) equals 01-07-1998 at midnight (00: hour). But your stored date has as specific time on it so it does not match. Remember Oracle dates are really datetimes!

Probably the best method is to truncate the time off the date:
> select * from table1 where trunc(the_date) = '1-JUL-98'

Hope this helps
-Frank

In article <359a0a15.0_at_per-nts1>,
  "Linda Harte" <Linda.Harte_at_peregrine.ie> wrote:
>
> We have several client server applications connecting to
> Oracle 7.3 and have just discovered some problems
> with dates.
>
> One application inserts sysdate into a table
> insert into table1 (the_date) values (sysdate)
>
> Another apllication selects data from this table and
> does some further processing and this is where the
> problem arises
> select * from table1 where the_date =
> to_char('01-07-1998','dd-mm-yyyy')
>
> This returns no rows although if we do a
> select the_date from table1 we see that
> there is one record with date 01-JUL-97.
>
> It appears that SYSDATE inserts the current
> time into the column as well and hence the
> problem when trying to select data.
>
> Any ideas on how to overcome this or how
> to deal with dates in oracle
>
> Thanks
>
> Linda
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Wed Jul 01 1998 - 08:18:05 CDT