Re: Linking two tables with Dates

date fields in oracle MAY contain exact time (otherwise 00:00:00). i suppose your tables both use date fields WITH or WITHOUT time. therefore a comparision with "<=" is here always right.

on the other hand: if you have two ore more entries in your payrate table with same date but different times, you might get wrong results if you join with TRUNC(date)!

johannes wahl

"Daniel A. Morgan" wrote:

> I would suggest that the two answers you have previously received may be missing
> an important piece of the puzzle.
>
> You can almost never write a WHERE clause like:
>
> WHERE date_field = date_value
>
> The reason is that date field in Oracle do not just contain the date. They also
> contain the time. Try this instead:
>
> WHERE TRUNC(date_field) = TRUNC(date_value)
>
> Daniel A. Morgan
>
> Zanfar Ali wrote:
>
> > I have two tables called work and payrate:
> >
> > in work there is two fields a date and a description.
> >
> > WORK
> > DATE DESCRIPTION
> > 20-Feb-2001 worked on sometething
> > 15-Feb-2001 worked on sometething
> > 14-Feb-2001 worked on sometething
> > 12-Feb-2001 worked on sometething
> >
> > in payrate i have a history of a persons payrates and the date it was set
> >
> > PAYRATE
> > DATSET RATE/Hour
> > 16-Feb-2001 23.45
> > 12-Feb-2001 21.45
> >
> > the dateset means that from 12-Feb-2001 to 15-Feb-2001 the payrate was 21.45
> > and then from 16-Feb-2001 to today the payrate was 23.45.This table can have
> > more entries.
> >
> > What i need to do is to link the work table entries with the corresponding
> > payrate...?
> >
> > Any help will be highly appreciated!
Received on Thu Feb 22 2001 - 11:40:11 CST