Re: Rectifying wrong Date outputs

On Mon, Mar 21, 2011 at 10:18 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Mar 21, 2011 at 9:57 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> What I was thinking was that YYYY would take either 2 or 4 digits.
>>> Whatever you do here, the year will have to be delimited by a non-digit
>>> for such cases to be parseable.
>
>> I was assuming a slightly more general variant of that - namely, Y,
>> YY, or YYY would all accept that many digits, or more; and the result
>> of Y with 2, 3, or 4 digits would be the same as if YY, YYY, or YYYY,
>> respectively, had been used.
>
> As far as I can see, that would completely destroy the use-case of
> trying to parse a string where there's not non-digit delimiters and
> so you have to take exactly the specified number of digits, not more.

Yeah, I thought about that, but it seems that use case is already
hopelessly broken in both PostgreSQL and Oracle, so I'm disinclined to
worry about it. If Piyush's table is to be believed, Oracle only
throws an error for the wrong number of digits if the format is Y or
YYY, and the actual number of digits is more. If the format is YY,
then it accepts 2, 3, or 4 digit years. And since YY is exponentially
more likely to be used than Y or YYY, that pretty much means you can't
do what you're talking about using this syntax anyway.

> Why not head in the other direction of allowing fewer digits than
> suggested by the format, instead of more?

Well, that seems a bit counterintuitive to me. I think it's much more
likely that someone wants to insist on a four-digit year (and not
allow just two digits) than that they want to insist on a two-digit
year (and not allow four digits). I also think that would be pretty
terrible for Oracle compatibility, since they're clearly interpreting
99 vs. YYYY as meaning either 0099, not 1999 or 2099. I don't think
we want to be randomly incompatible there.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company