1. sel ...
where cast(insert_timestamp as date) = date-1
2. sel ...
where insert_timestamp = date-1
in the above 2 scenarios, in the first one, i am converting the timestamp file to date and then comparing with a date. In the 2nd case i am not converting.
Both of them work fine.
From performance point of view which is better, the first one in which we explicitly convert or in the 2nd one where we dont convert (i believe teradata does it internally). In general is it better to cast or let teradata do it ( as in some cases TD does implicit conversion) from performance point of view ?
Sorry no - you should go for an explicit conversion with the cast as this is documenting what you want to do - at least as long as you can't prove that the implicit conversion is significant faster!
Beside performance you need to consider code readablity and maintainability!
cast(insert_timestamp as date) = date-1
it is clear that date is compared to date
insert_timestamp = date-1
you don't know - as long as you really test it - if date vs. date is compared or timestamp vs. timestamp
In the later case the result would be different.