TO_CHAR does not format MM and DD correctly when the month or day of the month is 1 digit

[GoogleCodeExporter]

The TO_CHAR function has a bug in it which causes dates where the month or day 
of the month is less than 10(ie 1 digit) to display incorrectly

What steps will reproduce the problem?

drop table if exists TOCHARBUG;
create table TOCHARBUG( test date);
insert into TOCHARBUG(test) values('2012-01-12');

select TO_CHAR(test, 'YYYYMMDD') from TOCHARBUG;

What is the expected output? What do you see instead?
Expected output is 20130112 but actual output is 2013112(which is ambiguous) 

What version of the product are you using? On what operating system, file
system, and virtual machine?
1.3.175

Do you know a workaround?
No, since you cannot override built-in functions

What is your use case, meaning why do you need this feature?
To maintain Oracle compatibility

How important/urgent is the problem for you?
Very important

Please provide any additional information below.
I have fixed the issue and provided a patch(with test cases indicating the 
problem)

Original issue reported on code.google.com by the.t...@gmail.com on 13 Feb 2014 at 2:09

Attachments:

• fix_h2_to_char_single_digit_bug.patch

[GoogleCodeExporter]

Could we get a license assignment statement from you please?

eg. see here: http://h2database.com/html/build.html#providing_patches

Thanks!

Original comment by noelgrandin on 28 Feb 2014 at 10:24

[GoogleCodeExporter]

Sure,

I wrote the code, it's mine, and I'm contributing it to H2 for distribution 
multiple-licensed under the H2 License, version 1.0, and under the Eclipse 
Public License, version 1.0 (http://h2database.com/html/license.html).

-Joel L. Tucci

Original comment by the.t...@gmail.com on 1 Mar 2014 at 3:15

[GoogleCodeExporter]

Sorry this took so long!

Committed in revision 5542.

This will be available in H2 version 1.3.176

Original comment by noelgrandin on 25 Mar 2014 at 7:48

• Changed state: Fixed