Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in01.usenetserver.com!news.usenetserver.com!news-in-02.newsfeed.easynews.com!news-in-01.newsfeed.easynews.com!core-easynews!easynews.com!easynews!en-nntp-09.dc1.easynews.com.POSTED!not-for-mail From: Walt Reply-To: walt_askier@SHOESyahoo.com Organization: Corwood Industries User-Agent: Thunderbird 2.0.0.14 (Windows/20080421) MIME-Version: 1.0 Newsgroups: comp.databases.oracle.server Subject: Re: How to join two tables while one is using Date and another is using Timestamp? References: <8a84044f-5b5a-4d70-b39b-d35470ba004a@w1g2000prd.googlegroups.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Lines: 46 Message-ID: <5yf4k.58107$dq6.55762@en-nntp-09.dc1.easynews.com> X-Complaints-To: abuse@easynews.com X-Complaints-Info: Please be sure to forward a copy of ALL headers otherwise we will be unable to process your complaint properly. Date: Thu, 12 Jun 2008 16:20:16 -0400 Xref: usenetserver.com comp.databases.oracle.server:445536 X-Received-Date: Thu, 12 Jun 2008 16:21:49 EDT (text.usenetserver.com) steph wrote: > On 12 Jun., 13:06, Mullin Yu wrote: >> How to join two tables while one is using Date and another is using >> Timestamp on Oracle, for example, >> >> select * from Orders o, AuditLog a >> where o.TradeDate = a.EventTs >> >> Orders >> ======= >> TradeDate (Date) >> e.g. 14-JUN-08 >> >> AuditLog >> ========= >> EventTs (Timestamp) >> e.g. 12-JUN-08 10.28.53.083000000 AM >> >> Thanks! >> >> Regards > > 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