Different results with and without to_date function

Toad for Oracle & DevOps Toolkit
1

Message from: srinivas27_750

Hi All,

We are using Oracle 10.2.0.5, AIX O.S.

We partitioned our transaction tables on a date column, i.e. we created monthly
partitions.

When we are running a SQL in SQL*Plus, Oracle is not returning data when our
condition requires to get the data from 2 partitions.

For Example:

SELECT

WHERE settlement_date BETWEEN ‘28-JUN-10’ AND ‘02-JUL-10’

Oracle needs to bring the data from Jun '10 and Jul '01 partitions. But
when we modify the SQL to include to_date function around the dates, oralce
returns the data.

SELECT

WHERE settlement_date BETWEEN to_date(‘28-JUN-10’, ‘dd-mon-yy’)
AND to_date(‘02-JUL-10’, ‘dd-mon-yy’)

Our DB default date format is dd-mon-yy, we also tried with YYYY format, but no
luck.

Our actual SQL includes many other conditions.

Our observations:

  1. If we use only one condition on the partitioned column, DB returns data.
  2. If we use all other conditions (around 10 conditions), along with the
    condition on the partitioned column, DB doesn’t return data.
  3. If we modify the SQL to include to_date function, DB returns data.
  4. If we change the end date to ‘31-dec-2099’, then DB returns data. In
    this case, oracle needs to scan the entire table.

Oracle is choosing different execution plan with to_date function and without
to_date function.

In one of Oracle documentation, we found that Oracle recommends to use to_date
function when we partition tables on a date column. They didn’t mention that
it is mandatory to use to_date function.

Anybody ran into the similar problem?, and did you resolve it without using the
to_date function?

We are using Business Objects Reports, if we need to use to_date function, then
we can’t use some of the Business Objects features, and we need to use those
features, so we are looking for a solution which doesn’t require to_date
function.

Thank you in advance.

Srini.

Historical Messages

Author: srinivas27_750
Date: Mon Dec 27 08:49:37 PST 2010

Hi All,

We are using Oracle 10.2.0.5, AIX O.S.

We partitioned our transaction tables on a date column, i.e. we created monthly
partitions.

When we are running a SQL in SQL*Plus, Oracle is not returning data when our
condition requires to get the data from 2 partitions.

For Example:

SELECT

WHERE settlement_date BETWEEN ‘28-JUN-10’ AND ‘02-JUL-10’

Oracle needs to bring the data from Jun '10 and Jul '01 partitions. But
when we modify the SQL to include to_date function around the dates, oralce
returns the data.

SELECT

WHERE settlement_date BETWEEN to_date(‘28-JUN-10’, ‘dd-mon-yy’)
AND to_date(‘02-JUL-10’, ‘dd-mon-yy’)

Our DB default date format is dd-mon-yy, we also tried with YYYY format, but no
luck.

Our actual SQL includes many other conditions.

Our observations:

  1. If we use only one condition on the partitioned column, DB returns data.
  2. If we use all other conditions (around 10 conditions), along with the
    condition on the partitioned column, DB doesn’t return data.
  3. If we modify the SQL to include to_date function, DB returns data.
  4. If we change the end date to ‘31-dec-2099’, then DB returns data. In
    this case, oracle needs to scan the entire table.

Oracle is choosing different execution plan with to_date function and without
to_date function.

In one of Oracle documentation, we found that Oracle recommends to use to_date
function when we partition tables on a date column. They didn’t mention that
it is mandatory to use to_date function.

Anybody ran into the similar problem?, and did you resolve it without using the
to_date function?

We are using Business Objects Reports, if we need to use to_date function, then
we can’t use some of the Business Objects features, and we need to use those
features, so we are looking for a solution which doesn’t require to_date
function.

Thank you in advance.

Srini.

__

2

Afternoon,

We partitioned our transaction tables on a date column, i.e. we created
monthly partitions.
Is the column actually a DATE? And if so, I presume that when you
partitioned, you used the to_date() funtion?

WHERE settlement_date BETWEEN '28-JUN-10' AND '02-JUL-10'
Those are strings, not dates. What happens is that the column,
settlement_date, will be converted to a string and compared with the two
strings you have given. Not the other way around.

Oracle needs to bring the data from Jun '10 and Jul '01 partitions. But
when we modify the SQL to include to_date function around the dates,
oralce returns the data.
Sounds like you did partition on a TO_DATE() function then, when you
select data from a partitioned table, the data you are selecting (for
the partition key) has to be in the format you used when you partitioned
it. So if you use a function to partition, you will need one to select
and to use partition pruning on the select as well.

This is similar to how function based indexes work.

WHERE settlement_date BETWEEN to_date('28-JUN-10', 'dd-mon-yy') AND
to_date('02-JUL-10', 'dd-mon-yy')
This is correct for all comparisons where a DATE column is involved. You
must convert from the string (or other) format to a DATE format for the
comparison to be correctly carried out. Especially as not using TO_DATE
relies on the implicit conversion being carried out by Oracle attempting
to convert using your NLS_DATE_FORMAT string, or, if that fails,
attempting to guess at the date format you supplied.

Our DB default date format is dd-mon-yy, we also tried with YYYY format,
but no luck.
Well, the date format is in lower case but your string values are UPPER!
Your dates are in dd-MON-yy format, not dd-mon-yy.

Our actual SQL includes many other conditions.
Makes no difference, you are converting a DATE to a string and checking
if the value is between two strings - you won't even get the correct
data out:

WHERE settlement_date BETWEEN '28-JUN-10' AND '02-JUL-10'

Means that you will probably be ok, but:

WHERE settlement_date BETWEEN '28-APR-10' AND '02-MAY-10'

means that you will also get stuff like '28-FEB-10' because that value
(as a string) is between the two values I gave above.

Our observations:

  1. If we use only one condition on the partitioned column, DB returns
    data.
    Possibly, but not necessarily the correct data all the time!
  1. If we use all other conditions (around 10 conditions), along with the
    condition on the partitioned column, DB doesn't return data.
    BEcause you are selecting a date converted to a string and comparing
    with a string. Don't do this!
  1. If we modify the SQL to include to_date function, DB returns data.
    Because now you are doing it correctly!

_4. If we change the end date to '31-dec-2099', then DB returns
data. _ In this case, oracle needs to scan the entire table.
Well, it's still a string! Yo are looking for every possible string in
your table which is less than or equal to '31-dec-2099' (which doesn't
match your date format!) , so if your starting date is '28-JUN-10' then
any date starting with '28' up to any date starting with a '31' will be
retuned.

The CBO knows this (did you EXPLAIN PLAN and check cardinality?) and
decides that a full scan will be required because partition pruning is
not going to help.

Oracle is choosing different execution plan with to_date function and
without to_date function.
Of course it is! You partitioned on a function (to_date) and you are
selecting on the same function, so Oracle can use partition pruning if
necessary. Without the function, you are comparing apples and oranges so
Oracle converts and because the partition key is not a string, you
cannot get the partition pruning to happen.

In one of Oracle documentation, we found that Oracle recommends to use
to_date function when we partition tables on a date column. They didn't
mention that it is mandatory to use to_date function.
It is mandatory to use a TO_DATE function when comparing table data with
literals or binds. Unless the comparison is between two identical data
types, Oracle will convert the column to match the other operand. It
should be the other way around.

Always always always, when selecting, updating, deleting or partitioning
by dates, compare dates with dates and nothing else. Be explicit and
always use TO_DATE or TO_CHAR etc.

Anybody ran into the similar problem?, and did you resolve it without
using the to_date function?
Yes, may times with third parties who write bad code where dates are
concerned. What we did was exactly as described above - we were
explicit. (Plus we gave the vendor a right good telling off!)

We are using Business Objects Reports, if we need to use to_date
function, then we can't use some of the Business Objects features, and
we need to use those features, so we are looking for a solution which
doesn't require to_date function.
You can't! As far as I know, business objects allows you to build a
universe with dates in it. We have lots of BusObj at out work and so
far, we've never had complaints about not being able to use certain
features of BO because we have DATE columns. Dates in Oracle have been
around longer than BO - so I suspect you may be being misled. (But I'l
willing to be convinced otherwise!)

Thank you in advance.
Welcome. Happy New Year..

--

Cheers,
Norm. [TeamT]

3

A late Happy Holidays to All!

Those are strings, not dates.

I agree with Norm completely on all his points. However, it should be noted that this is not only an "issue" with dates but with any conversion.

I put issue in quotes because the issue is with the developer, not the language. And I say this as an experienced developer :wink:

In order to produce robust code that's not going to break the moment a database setting (or anything else) is changed (like altering the nls_date_format from one format to another), some best practices:

  1. Always explicitly identify your format if you are not dealing with a date. This is a best practice that all developers should follow for any datatype conversion, not just with dates.

  2. If you're going to store the date in a text format (and you really shouldn't) then you should be sure to choose the only format that will sort properly: yyyymmdd (format 00000000)

If one is going to develop code that works with dates while ignoring those two best practices, one should not be surprised when one runs into all kinds of bugs and situations where significantly more complex code will be required to handle the situation correctly.

Guess who never had to change his code when Y2K came creeping along :slight_smile:

It may seem like a shortcut for the developer at the time to avoid explicitly identifying date conversions, but the reality is:

i) Development time is significantly more costly in the long run with regards maintenance.

ii) Development time, as well as keystrokes, to build the more complicated code to handle the situations which should be automatic is always significantly more than if you had just been explicit to begin with.

Roger S.

This communication, including any attached documentation, is intended only for the person or entity to which it is addressed, and may contain confidential, personal and/or privileged information. Any unauthorized disclosure, copying, or taking action on the contents is strictly prohibited. If you have received this message in error, please contact us immediately so we may correct our records. Please then delete or destroy the original transmission and any subsequent reply.

4

Good advice indeed and so true. Here in Europe it is very important. I live in
Holland and normally it is DD/MM/YYYY and having a system that also runs in the
UK (MM/DD/YYYY) as in Sweden (YYYY/MM/DD) is sometime a very big challenge. And
another conversion problem is the decimal comma or point.

Groetjes,
Wim

On Wed, Dec 29, 2010 at 18:58, Simoneau, Roger
wrote:

A late Happy Holidays to All!

Those are strings, not dates.

I agree with Norm completely on all his points. However, it should be noted
that this is not only an "issue" with dates but with any conversion.

I put issue in quotes because the issue is with the developer, not the
language. And I say this as an experienced developer ;)

In order to produce robust code that's not going to break the moment a
database setting (or anything else) is changed (like altering the
nls_date_format from one format to another), some best practices:

1) Always explicitly identify your format if you are not dealing with a
date. This is a best practice that all developers should follow for any
datatype conversion, not just with dates.

2) If you're going to store the date in a text format (and you really
shouldn't) then you should be sure to choose the only format that will
sort properly: yyyymmdd (format 00000000)
"01" to represent the first day of the month, anything else and
you have to write more complicated parsing mechanisms to handle the date
computations. The more complex the code, the more bugs can creep in.

If one is going to develop code that works with dates while ignoring those
two best practices, one should not be surprised when one runs into all kinds
of bugs and situations where significantly more complex code will be
required to handle the situation correctly.

Guess who never had to change his code when Y2K came creeping along :)

It may seem like a shortcut for the developer at the time to avoid
explicitly identifying date conversions, but the reality is:

i) Development time is significantly more costly in the long run with
regards maintenance.

ii) Development time, as well as keystrokes, to build the more complicated
code to handle the situations which should be automatic is always
significantly more than if you had just been explicit to begin with.

Roger S.

This communication, including any attached documentation, is intended only
for the person or entity to which it is addressed, and may contain
confidential, personal and/or privileged information. Any unauthorized
disclosure, copying, or taking action on the contents is strictly
prohibited. If you have received this message in error, please contact us
immediately so we may correct our records. Please then delete or destroy the
original transmission and any subsequent reply.
5

Wim,

On 30/12/10 19:10, Wim de Lange wrote:

Good advice indeed and so true. Here in Europe it is very important. I
live in Holland and normally it is DD/MM/YYYY and having a system that
also runs in the UK (MM/DD/YYYY)
Eh? Did you mean USA there by any chance? We UK-ites also do DD/MM/YYYY
but the US does MM/DD/YYYY hence 9/11 rather than 11/9 as we say.

My wife Alison worked in a Large building Society that had
sub-contracted out to a US company for development. She was a tester.
They (in the US) used to get extremely uppity when she flagged faults in
their date handling as the application would only accept dates in
MM/DD/YYYY format. They refused to fix "something that was not broken"
and lost the contract.

as in Sweden (YYYY/MM/DD) is sometime a
very big challenge. And another conversion problem is the decimal comma
or point.
You probably know this already but :

select to_char(1234567890.123, '9G999G999G999D999') from dual;

will convert into the session's required format - given that the session
has a valid NLS_NUMERIC_CHARACTERS parameter. Granted, it's probably not
as simply converting from whatever the user sent you of course!

--

Cheers,
Norm. [TeamT]

6

On 31/12/10 13:54, Norman Dunbar wrote:

Granted, ... not as simply converting ...
I of course wanted to type "not as simple as converting" but for some
reason, my fingers refused! :slight_smile:

--
Cheers,
Norm. [TeamT]

7

Roger,

I have to agree with you on all of your points… especially the ones about
maintenance time. When you specify exactly what you’re looking for, it’s also
much clearer for the folks who do follow-up maintenance (which may be the same
person who wrote the code in the first place).

I personally work on so many areas of different applications, that if I go back
to the code after a year, many times it’s like starting over. groan…

thus, in my own self-interest, it really helps me to make everything as explicit
as possible…

Steve…