Re: Strange Date behaviour

A date is not a string; a date is a date. You need to understand that a date on the screen is a string representation of a date. Compare dates with dates.
eg
select * from OUR_WORK where WORK_DATE=to_date('31-MAR-03','dd-mmm-yy'); which is a bad choice since you are specifying a 2 digit year, use 4 digit years.
eg
select * from OUR_WORK where WORK_DATE=to_date('31-MAR-2003','dd-mmm-yyyy');

So what your select statement is saying is get me all the records where the date and time is midnight on the 31st of March. There aren't any. If you want all the records that are there on the 31 of march then select * from OUR_WORK where WORK_DATE>=to_date('31-MAR-03','dd-mmm-yy') and WORK_DATE<to_date('01-APR-03','dd-mmm-yy');

which means on or after midnight of 31st and before the 1st of April.

Use to_date, don't rely on a default date format; it could change. Use 4 digit years, remember the y2K problem? Don't compare strings to dates; compare dates to dates.

Jim

--
Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
with family.  Remove the negative part, keep the minus sign.  You can figure
it out.
"Kenanje" <jannekeh_at_hotmail.com> wrote in message
news:8b98957.0304010612.2a7cf9bd_at_posting.google.com...


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