Date conversion in Oracle part 2
It’s a follow-up to the previous post.
As it turned out, implicit date conversions may also prevent Oracle from
doing the partition
For example, if you have a table
INVOICES with a range partition on
INVOICE_DATE field, then expression
SELECT ... WHERE invoice_date >= '01-MAR-09' AND invoice_date < '02-MAR-09'
will not perform the partition pruning, whereas
SELECT ... WHERE invoice_date >= TO_DATE('01/03/2009', 'DD/MM/YYYY') AND invoice_date < TO_DATE('02/03/2009', 'DD/MM/YYYY')
Because the efficiency of partition pruning is usually why partitioning is used in the first place, the choice is obvious.
But after all, I’d use
SELECT ... WHERE invoice_date BETWEEN TO_DATE( '01/03/2009', 'DD/MM/YYYY') AND TO_DATE( '01/03/2009', 'DD/MM/YYYY') + 1 - 1/24/3600
BETWEEN operation is specifically tailored for such situations.
“1/24/3600” here represents 1 second, and the whole statement should be
read as “From 01 March 2009 0:00am to 01 March 2009 11:59pm”.