Thread: FM format modifier does not remove leading zero from year

Command:

select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY')

Result observed:

1.1.09

Result expected:

1.1.9

How to fix ?

Andrus.

On Saturday 02 January 2010 10:14:07 am Andrus wrote:
> Command:
>
> select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY')
>
> Result observed:
>
> 1.1.09
>
> Result expected:
>
> 1.1.9
>
> How to fix ?
>
> Andrus.

From what I could see in the source code (src/backend/utils/adt/formatting.c)
the year portion of the string is not run through the FM modifier. A fix would
mean a patch to the above AFAIK.

--
Adrian Klaver
aklaver@comcast.net

Adrian Klaver <aklaver@comcast.net> writes:
> From what I could see in the source code
> (src/backend/utils/adt/formatting.c) the year portion of the string is
> not run through the FM modifier. A fix would mean a patch to the above
> AFAIK.

Should it be?  Can anyone check how this works on Oracle?

            regards, tom lane

On 1/5/2010 10:54 AM, Tom Lane wrote:
> Adrian Klaver<aklaver@comcast.net>  writes:
>>  From what I could see in the source code
>> (src/backend/utils/adt/formatting.c) the year portion of the string is
>> not run through the FM modifier. A fix would mean a patch to the above
>> AFAIK.
>
> Should it be?  Can anyone check how this works on Oracle?
>

Oracle states clearly in the SQL Reference manual:

"A modifier can appear in a format model more than once. In such a case,
each subsequent occurrence toggles the effects of the modifier."

I get the following results:

select to_char(DATE'2009-1-1','FMDD.FMMM.FMYY') from dual;

1.01.9

select to_char(DATE'2009-1-1','FM DD.MM.YY') from dual

1.1.9

--
Guy Rouillier

Guy Rouillier <guyr-ml1@burntmail.com> writes:
> Oracle states clearly in the SQL Reference manual:

> "A modifier can appear in a format model more than once. In such a case,
> each subsequent occurrence toggles the effects of the modifier."

*Toggles* the effect of the modifier?  Egad, what drunken idiot chose
that specification?

That's certainly not how PG is interpreting the FM modifier --- we
suppose it applies to just the specifier it's attached to.  Still,
we generally assume that Oracle is the authoritative reference for
how to_char() ought to behave, so maybe we should hold our noses
and change it.  Problem is that this would inevitably break
practically every existing use of to_date with FM :-( (not to mention
the other modifiers, which I suppose behave that way too?)

Anyway, your example clearly shows that FM ought to strip leading
zeroes in YY, so that change ought to get made.

            regards, tom lane

On 1/6/2010 3:29 PM, Tom Lane wrote:
> Guy Rouillier<guyr-ml1@burntmail.com>  writes:
>> Oracle states clearly in the SQL Reference manual:
>
>> "A modifier can appear in a format model more than once. In such a case,
>> each subsequent occurrence toggles the effects of the modifier."
>
> *Toggles* the effect of the modifier?  Egad, what drunken idiot chose
> that specification?

Eh, tomato, tomahto.  If you assume that someone will strip leading
zeroes consistently, the Oracle approach makes sense.  That would be a
reasonable assumption to make; why would I strip the zero off the month
but leave it on the day?  So, in the unusual case that you want to do
such a thing, you are asked to use a second occurrence of FM to turn
zero suppression back off.

--
Guy Rouillier