All Forums Teradata Applications
rohit.lalwani1 8 posts Joined 08/13
18 Jun 2014
Converting TimeStamp into Date

Hi 
I have two timestamp fields which i'm using in joining condition but values are slight different in it like below.
Posting Date   : value(2014-01-09 00:00:00)
Eff_from          : value(2014-01-09 15:08:29.000000)
Join condition  : Posting date >=  Eff_from   { this shuld be true in my code for above values but it's failing because of time}
I can use CAST function and convert above timestamp into DATE and then compare but casting is very expensive and my query is taking lot of time.
Is there any other way to do this. ?  it'll be really helpful .
Thanks is advance 

 

Tags:
Raja_KT 1246 posts Joined 07/09
18 Jun 2014

To avoid casting- for now I can think of is to make both the joining fields consistently the same format in ddl, if a frequent join and query. 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

dnoeth 4628 posts Joined 11/04
19 Jun 2014

Did you check Explain/DBQL why it's slow? Compare Explains with/without cast, it might be a bad plan due to loosing statistics on the casted column.
You can also add one day to "Posting Date":

"Posting date" + INTERVAL '1' DAY > Eff_from

It's still a cast, but maybe this table is smaller and/or the optimizer does a different plan.
Otherwise it's Raja's "fix your data model" :-)
Btw, looks like those tables were ported from a system whithout different datatypes for DATE/TIME/TIMESTAMP. Oracle?

Dieter

ramesh_td 9 posts Joined 01/14
23 Jun 2014

Hi Rohit,
can you please paste your sample code that perform the operation here?
you are trying to compare the date along with timestamp?

You must sign in to leave a comment.