Re: How to join two tables while one is using Date and another is using Timestamp?

steph wrote:

> On 12 Jun., 13:06, Mullin Yu <mullin..._at_gmail.com> wrote:

> 
> by removing the time component of the time stamp like:
> 
> select * from Orders o, AuditLog a
> where o.TradeDate = trunc(a.EventTs)

Joining on a timestamp and date field? Man that's ugly. Much better to   fk the auditlog to the orders table and do the lookup by the pk of the orders table. Assuming you can't change this horrific data design:

Using trunc() is one way to do it, but as written it'll only work if the tradedate field has no time component (i.e. all values are midnight).

Depending on whether you want to join on the same day, or the same second, look at the second parameter to trunc() (or maybe even consider using round()). Of course, the query will be dog slow with these functions.

Have you tried letting the implicit conversion do it's magic? It *should* simply snip off fractional seconds when converting timestamp to a date, but I haven't tried this.

//Walt Received on Thu Jun 12 2008 - 15:20:16 CDT