Which one is better?

Database
Enthusiast

Which one is better?

Hi!

I have a qry which has a where clause as :
WHERE CAST(DT AS CHAR(10)) BETWEEN '2000-01-01' AND '2011-01-01')

Can changing this to 'Cast as date' OR Extract (date) improve the qry and still give same results? DT is a timestamp field. Basiclly my qsn is , shd we convert a timestamp to date or character for comparsion? CAST uses to much memory so wud Extract be a batter option?

WHERE CAST(DT AS date )BETWEEN '2000-01-01' AND '2011-01-01')

Thanks!!!

1 REPLY
Senior Apprentice

Re: Which one is better?

Typecasting to a CHAR is worst case, every value in every row needs to be typecasted and all statistics are lost.

And a DATE literal should always include the DATE keyword: DATE '2000-01-01'

WHERE DT BETWEEN date '2000-01-01' AND date '2011-01-01'
is better, but stats might be lost, too, you should check that.

Best case is no typecast at all:
WHERE DT BETWEEN timestamp '2000-01-01 00:00:00' AND timestamp '2011-01-01 23:59:59'

Dieter