Re: SQL join on date

From: Gary O'Keefe <gary_at_onegoodidea.com>
Date: Thu, 29 Jul 1999 15:38:58 GMT
Message-ID: <37a07326.25220635@news.hydro.co.uk>

Blue wrote:

>I would like to join two tables by a date field..
>
>The first table always has a date plus a time value in the date field.
>
>The second table always has a just a date value, time is always 0
>
>What is the best SQL join statement to link them based on the date
>value only, ignoring time
>
>Sorry if this is a stupid question but Oracle through impromptu locks
>up if I use a function to convert the date-time to a date.

Have you tried anything like this

SELECT	table1.*, table2.*
FROM	table1, table2
WHERE	trunc(table1.datetime) = table2.date;

or was your date conversion performed using to_char, like

SELECT	table1.*, table2.*
FROM	table1, table2
WHERE	to_char(table1.datetime, 'YYYY/MM/DD') = to_char(table2.date,

'YYYY/MM/DD'); in which case it may not have locked, it may just be very, very slow.

A fragment of your code would be helpful to determine a starting point.

Gary
--
Gary O'Keefe
gary_at_onegoodidea.com

You know the score - my current employer has nothing to do with what I post Received on Thu Jul 29 1999 - 10:38:58 CDT