Converting TimeStamp into Date

Teradata Applications

Converting TimeStamp into Date


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 (1)

Re: Converting TimeStamp into Date

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. 

Junior Contributor

Re: Converting TimeStamp into Date

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?


Re: Converting TimeStamp into Date

Hi Rohit,

can you please paste your sample code that perform the operation here?

you are trying to compare the date along with timestamp?