Re: Strange Date behaviour

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.

--
Posted via http://dbforums.com