Different results with and without to_date function

Toad for Oracle & DevOps Toolkit
1

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

Message from: wimdelange_062

These are not dates: BETWEEN ‘28-JUN-10’ AND ‘02-JUL-10’ You are relying on the
implicit conversions from string to dates. That is a big risk to take.
Conversion could be changing depending OS, application, etc. So it is not
strange that the systems behaves strangely. Date partioning should be no
problem, but you must be sure that your queries are always using correct dates,
without implicit conversions. Groetjes, Wim On Mon, Dec 27, 2010 at 17:52, {Toad
for Oracle} on behalf of

Historical Messages

Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010
These are not dates: BETWEEN ‘28-JUN-10’ AND ‘02-JUL-10’ You are relying on the
implicit conversions from string to dates. That is a big risk to take.
Conversion could be changing depending OS, application, etc. So it is not
strange that the systems behaves strangely. Date partioning should be no
problem, but you must be sure that your queries are always using correct dates,
without implicit conversions. Groetjes, Wim On Mon, Dec 27, 2010 at 17:52, {Toad
for Oracle} on behalf of
__

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.

__

3

These are not dates: BETWEEN ‘28-JUN-10’ AND ‘02-JUL-10’

You are relying on the implicit conversions from string to dates. That is a
big risk to take. Conversion could be changing depending OS, application,
etc.

So it is not strange that the systems behaves strangely. Date partioning
should be no problem, but you must be sure that your queries are always
using correct dates, without implicit conversions.

Groetjes,
Wim

On Mon, Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of

4

Hi Wim,

On 27/12/10 17:14, Wim de lange wrote:

These are not dates: BETWEEN '28-JUN-10' AND '02-JUL-10' You are relying
on the implicit conversions from string to dates.
Nope. With the table column on the left side of the comparison, the
conversion will be DATE column to string. Then compare.

This leads to all sorts of broken data getting through. Depending on the
start and end points of the 'between'. For example:

... BETWEEN '07-apr-2010' and '10-may-2010'

Will allow '08-dec-2010' as a valid value because, as a string, it
passes the test. As a DATE it doesn't.

--

Cheers,
Norm. [TeamT]

5

Message from: wimdelange_062

Hi Norm, I was suspecting that there is something wrong with the conversions but
was not specific in which direction. The full explanation given by you confirms
what I thought what was happening, but had good details. I liked especially the
following: > 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 Groetjes, Wim
On Tue, Dec 28, 2010 at 16:38, {Toad for Oracle} on behalf of {Norman

Historical Messages

Author: Wim de lange
Date: Tue Dec 28 23:33:15 PST 2010
Hi Norm, I was suspecting that there is something wrong with the conversions but
was not specific in which direction. The full explanation given by you confirms
what I thought what was happening, but had good details. I liked especially the
following: > 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 Groetjes, Wim
On Tue, Dec 28, 2010 at 16:38, {Toad for Oracle} on behalf of {Norman
__

Author: Norman Dunbar
Date: Tue Dec 28 07:09:29 PST 2010
Hi Wim, On 27/12/10 17:14, Wim de lange wrote: > These are not dates: BETWEEN
‘28-JUN-10’ AND ‘02-JUL-10’ You are relying > on the implicit conversions from
string to dates. Nope. With the table column on the left side of the comparison,
the conversion will be DATE column to string. Then compare. This leads to all
sorts of broken data getting through. Depending on the start and end points of
the ‘between’. For example: … BETWEEN ‘07-apr-2010’ and ‘10-may-2010’ Will
allow ‘08-dec-2010’ as a valid value because, as a string, it passes the test.
As a DATE it doesn’t. – Cheers, Norm. [TeamT]
__

Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010
These are not dates: BETWEEN ‘28-JUN-10’ AND ‘02-JUL-10’ You are relying on the
implicit conversions from string to dates. That is a big risk to take.
Conversion could be changing depending OS, application, etc. So it is not
strange that the systems behaves strangely. Date partioning should be no
problem, but you must be sure that your queries are always using correct dates,
without implicit conversions. Groetjes, Wim On Mon, Dec 27, 2010 at 17:52, {Toad
for Oracle} on behalf of
__

Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010
Message from: wimdelange_062 These are not dates: BETWEEN ‘28-JUN-10’ AND
‘02-JUL-10’ You are relying on the implicit conversions from string to dates.
That is a big risk to take. Conversion could be changing depending OS,
application, etc. So it is not strange that the systems behaves strangely. Date
partioning should be no problem, but you must be sure that your queries are
always using correct dates, without implicit conversions. Groetjes, Wim On Mon,
Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of
_______________________________________ Historical Messages Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010 These are not dates: BETWEEN ‘28-JUN-10’ AND
‘02-JUL-10’ You are relying on the implicit conversions from string to dates.
That is a big risk to take. Conversion could be changing depending OS,
application, etc. So it is not strange that the systems behaves strangely. Date
partioning should be no problem, but you must be sure that your queries are
always using correct dates, without implicit conversions. Groetjes, Wim On Mon,
Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of __ 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. __

__

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.

__

6

Hi Norm,

I was suspecting that there is something wrong with the conversions but was
not specific in which direction. The full explanation given by you confirms
what I thought what was happening, but had good details. I liked especially
the following:

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

Groetjes,
Wim

On Tue, Dec 28, 2010 at 16:38, {Toad for Oracle} on behalf of {Norman

7

Message from: srinivas27_750

Thank you all for your replies about my question.

But, in our case something else is going wrong,

If we are going with the following assumption

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

it should not return the data for the following statement also:

SELECT * FROM WHERE settlement_date BETWEEN '28-JUN-10'
AND '02-JUL-10'

but, it returns the expected data.

in our case, oracle is converting dates on the right side to date values and
comparing with the left side column, and in some cases it is not doing the same.

anybody knows, why oracle is behaving differently with different statements.

Thank you,
Srini.

Historical Messages

Author: srinivas27_750
Date: Sun Jan 02 17:20:39 PST 2011

Thank you all for your replies about my question.

But, in our case something else is going wrong,

If we are going with the following assumption

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

it should not return the data for the following statement also:

SELECT * FROM WHERE settlement_date BETWEEN '28-JUN-10'
AND '02-JUL-10'

but, it returns the expected data.

in our case, oracle is converting dates on the right side to date values and
comparing with the left side column, and in some cases it is not doing the same.

anybody knows, why oracle is behaving differently with different statements.

Thank you,
Srini.

__

Author: Wim de lange
Date: Tue Dec 28 23:33:15 PST 2010
Hi Norm, I was suspecting that there is something wrong with the conversions but
was not specific in which direction. The full explanation given by you confirms
what I thought what was happening, but had good details. I liked especially the
following: > 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 Groetjes, Wim
On Tue, Dec 28, 2010 at 16:38, {Toad for Oracle} on behalf of {Norman
__

Author: Wim de lange
Date: Tue Dec 28 23:33:15 PST 2010
Message from: wimdelange_062 Hi Norm, I was suspecting that there is something
wrong with the conversions but was not specific in which direction. The full
explanation given by you confirms what I thought what was happening, but had
good details. I liked especially the following: > 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 Groetjes, Wim On Tue, Dec 28, 2010 at 16:38,
{Toad for Oracle} on behalf of {Norman _______________________________________
Historical Messages Author: Wim de lange Date: Tue Dec 28 23:33:15 PST 2010 Hi
Norm, I was suspecting that there is something wrong with the conversions but
was not specific in which direction. The full explanation given by you confirms
what I thought what was happening, but had good details. I liked especially the
following: > 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 Groetjes, Wim
On Tue, Dec 28, 2010 at 16:38, {Toad for Oracle} on behalf of {Norman __ Author:
Norman Dunbar Date: Tue Dec 28 07:09:29 PST 2010 Hi Wim, On 27/12/10 17:14, Wim
de lange wrote: > These are not dates: BETWEEN '28-JUN-10' AND '02-JUL-10' You
are relying > on the implicit conversions from string to dates. Nope. With the
table column on the left side of the comparison, the conversion will be DATE
column to string. Then compare. This leads to all sorts of broken data getting
through. Depending on the start and end points of the 'between'. For example:
... BETWEEN '07-apr-2010' and '10-may-2010' Will allow '08-dec-2010' as a valid
value because, as a string, it passes the test. As a DATE it doesn't. -- Cheers,
Norm. [TeamT] __ Author: Wim de lange Date: Mon Dec 27 09:14:15 PST 2010 These
are not dates: BETWEEN '28-JUN-10' AND '02-JUL-10' You are relying on the
implicit conversions from string to dates. That is a big risk to take.
Conversion could be changing depending OS, application, etc. So it is not
strange that the systems behaves strangely. Date partioning should be no
problem, but you must be sure that your queries are always using correct dates,
without implicit conversions. Groetjes, Wim On Mon, Dec 27, 2010 at 17:52, {Toad
for Oracle} on behalf of __ Author: Wim de lange Date: Mon Dec 27 09:14:15 PST
2010 Message from: wimdelange_062 These are not dates: BETWEEN '28-JUN-10' AND
'02-JUL-10' You are relying on the implicit conversions from string to dates.
That is a big risk to take. Conversion could be changing depending OS,
application, etc. So it is not strange that the systems behaves strangely. Date
partioning should be no problem, but you must be sure that your queries are
always using correct dates, without implicit conversions. Groetjes, Wim On Mon,
Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of
_______________________________________ Historical Messages Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010 These are not dates: BETWEEN '28-JUN-10' AND
'02-JUL-10' You are relying on the implicit conversions from string to dates.
That is a big risk to take. Conversion could be changing depending OS,
application, etc. So it is not strange that the systems behaves strangely. Date
partioning should be no problem, but you must be sure that your queries are
always using correct dates, without implicit conversions. Groetjes, Wim On Mon,
Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of __ 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 SQLPlus, 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. __
_______________________________________ __ 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 SQLPlus, 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. __

__

Author: Norman Dunbar
Date: Tue Dec 28 07:09:29 PST 2010
Hi Wim, On 27/12/10 17:14, Wim de lange wrote: > These are not dates: BETWEEN
'28-JUN-10' AND '02-JUL-10' You are relying > on the implicit conversions from
string to dates. Nope. With the table column on the left side of the comparison,
the conversion will be DATE column to string. Then compare. This leads to all
sorts of broken data getting through. Depending on the start and end points of
the 'between'. For example: ... BETWEEN '07-apr-2010' and '10-may-2010' Will
allow '08-dec-2010' as a valid value because, as a string, it passes the test.
As a DATE it doesn't. -- Cheers, Norm. [TeamT]
__

Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010
These are not dates: BETWEEN '28-JUN-10' AND '02-JUL-10' You are relying on the
implicit conversions from string to dates. That is a big risk to take.
Conversion could be changing depending OS, application, etc. So it is not
strange that the systems behaves strangely. Date partioning should be no
problem, but you must be sure that your queries are always using correct dates,
without implicit conversions. Groetjes, Wim On Mon, Dec 27, 2010 at 17:52, {Toad
for Oracle} on behalf of
__

Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010
Message from: wimdelange_062 These are not dates: BETWEEN '28-JUN-10' AND
'02-JUL-10' You are relying on the implicit conversions from string to dates.
That is a big risk to take. Conversion could be changing depending OS,
application, etc. So it is not strange that the systems behaves strangely. Date
partioning should be no problem, but you must be sure that your queries are
always using correct dates, without implicit conversions. Groetjes, Wim On Mon,
Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of
_______________________________________ Historical Messages Author: Wim de lange
Date: Mon Dec 27 09:14:15 PST 2010 These are not dates: BETWEEN '28-JUN-10' AND
'02-JUL-10' You are relying on the implicit conversions from string to dates.
That is a big risk to take. Conversion could be changing depending OS,
application, etc. So it is not strange that the systems behaves strangely. Date
partioning should be no problem, but you must be sure that your queries are
always using correct dates, without implicit conversions. Groetjes, Wim On Mon,
Dec 27, 2010 at 17:52, {Toad for Oracle} on behalf of __ 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. __

__

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.

__

8

Thank you all for your replies about my question.

But, in our case something else is going wrong,

If we are going with the following assumption

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

it should not return the data for the following statement also:

SELECT * FROM WHERE settlement_date BETWEEN '28-JUN-10' AND '02-JUL-10'

but, it returns the expected data.

in our case, oracle is converting dates on the right side to date values and comparing with the left side column, and in some cases it is not doing the same.

anybody knows, why oracle is behaving differently with different statements.

Thank you,
Srini.

9

On 03/01/11 01:20, srinivas27_750 wrote:

...

in our case, oracle is converting dates on the right side to date values
and comparing with the left side column, and in some cases it is not
doing the same.

anybody knows, why oracle is behaving differently with different statements.
Probably, and I don't know for certain, it's because you are comparing
apples and oranges and something in the conversion is not working as
expected.

It could be related to the version of Oracle and/or client that you are
using - could be anything.

The solution is simple though, you must always specify "to_date" when
comparing a date column with a string.

And, as has been highlighted elsewhere, whenever you attempt to compare
two data types that are not the same, so strings and numbers, numbers
and dates, and so on.

Be explicit, always!

Happy New Year.

--
Cheers,
Norm. [TeamT]

10

But, in our case something else is going wrong,

Question: if you explicitly do the conversion yourself, does the problem go
away?

Note: I am assuming the data column “settlement_date” is actually
defined as a date. Please confirm this assumption. And if correct, confirm if
the issue disappears when you explicitly do the date conversion yourself.

Roger S.

11

Morning Roger,

Question: if you explicitly do the conversion yourself, does
the problem go away?
I believe that the original post said exactly that - when I use to_date,
it works.

Note: I am assuming the data column "settlement_date" is
actually defined as a date. Please confirm this assumption.
I asked this too - but from the above, it certainly appears that the
column is a date otherwise, how would to_date make it work. The OP has
yet to confirm though.

And if correct, confirm if the issue disappears when you
explicitly do the date conversion yourself.
This is the whole problem, the OP is comparing apples and dates (ha ha -
a fruity joke!) and until the comparison is between dates and dates,
there's no guarantee that any of the data retrieved is correct.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

12

Hey Norm;

I believe that the original post said exactly that

Yea… but perhaps if we have the OP think about the situation from several
angles understanding with regards why the solutions that were offered were
offered might occur.

At least, “they” say repetition aids understanding :slight_smile:

Roger S.

13

Hi Roger,

I believe that the original post said exactly that
Yea... but perhaps if we have the OP think about the situation
from several angles understanding with regards why the
solutions that were offered were offered might occur.
Well, I hate to be blunt (advance warning: There is a "but" coming),
but, there is nothing to think of - the OP MUST convert strings to dates
explicitly in order to get correct results. It really is that simple!
:wink:

At least, "they" say repetition aids understanding :slight_smile:
Hopefully, by now, I've said "be explicit" often enough then! :wink:

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk