Re: Strange Date behaviour

Thanks for your advise!

We checked the timestamps for the records and indeed they were different.

We probably encountered this problem now, because we only use this program for booking since january and last weekend we switched from wintertime to summertime.

King Regards,

Janneke

andrewst <member14183_at_dbforums.com> wrote in message news:<2715662.1049211732_at_dbforums.com>...
> Originally posted by Kenanje
> > I use a oracle database for timeregistration.
> >
> > I have one table; our_work, where I have a record work_date (with
> > format DATE).
> >
> > There are records filled with data from the past two years until today
> > (1 april 2003). There are also records with work_date: 31-MAR-03.
> >
> > When I do the followong select statement:
> >
> > select * from OUR_WORK;
> >
> > I get all the records, also the ones with work_date 31-MAR-03. (so
> > there are really record with that date in there.
> >
> > When I do: select * from OUR_WORK where WORK_DATE='31-MAR-03';
> >
> > "no rows selected"
> >
> > and when I do: select * from OUR_WORK where WORK_DATE like
> > '31-MAR-03';
> >
> > The rows with work_date 31-MAR-03 appear.
> >
> > Who knows what kind of problems I am looking at?
> >
> > Thanks in advanced.
> It is because DATE columns also have a time component. When you say:
>
> select * from OUR_WORK where WORK_DATE='31-MAR-03';
>
> you are relying on a default date format mask of DD-MON-YY that has no
> time component, i.e time is 00:00:00. If any WORK_DATE has a time
> component other than 00:00:00, it will not be matched.
>
> Try one of these instead:
>
> select * from OUR_WORK where
> trunc(WORK_DATE)=TO_DATE('31-MAR-2003','DD-MON-YYYY'); -- uses TRUNC
> function to ignore the time in WORK_DATE
>
> select * from OUR_WORK where WORK_DATE >=
> TO_DATE('31-MAR-2003','DD-MON-YYYY') and WORK_DATE <
> TO_DATE('01-APR-2003','DD-MON-YYYY');
>
> The second version, while more verbose, allows use of an index on
> WORK_DATE.
Received on Wed Apr 02 2003 - 03:17:56 CST