Re: BUG #4862: different results in to_date() between 8.3.7 & 8.4.RC1

2009/6/22 Jeremy Ford <jeremford(at)gmail(dot)com>:
> Oracle 9i:
>  YEAR MONTH METHOD1     METHOD2
>
>  2009 03   1/03/2009   1/03/2009
>
> Oracle 10g:
> YEAR      MONTH                METHOD1           METHOD2
>
>  2009      03          1/03/2009            1/03/2009
>

Hi Jeremy,

The query you used above might not tell us the whole story, because
you also have calls to to_char() in there (which is where those
leading spaces are coming from), and I have no idea whether Oracle's
to_char() also adds those leading spaces.

It would be very helpful if you'd test the following against Oracle
and let us know what you get back, just to totally rule out any
interference from to_char():

SELECT to_date(' 2009 03', 'YYYYMM') as nospace;
SELECT to_date(' 2009 03', 'YYYY MM') as monthspace;
SELECT to_date(' 2009 03', ' YYYY MM') as bothspaces;
SELECT to_date(' 2009 03', ' YYYY MM') as extraspace;
SELECT to_date('2009 03', ' YYYY MM') as bogusspace;

> On Sat, Jun 20, 2009 at 2:03 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> My feeling about it is that we usually try to match Oracle's behavior
>> for to_date/to_char, so the $64 question is whether Oracle allows a
>> leading space in these same cases.  Anyone have it handy to test?

If it turns out that Oracle does ignore leading spaces, we might want
to just add a special case in the input string scanner to skip over
any whitespace at the beginning of the string before we begin
processing in earnest.

Depending on the results from the test cases I posted above, we might
want to do something similar for the format string also.

Cheers,
BJ