Re: two minor issues with date time types

Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
> On Thu, 14 Aug 2003, scott.marlowe wrote:
>> insert into test values ('20021131');
>> invalid input syntax for timestamp: "20021131"
>>
>> SQL92 and 99 say it should be:
>> data exception-datetime field overflow

> I don't think that's intended to be the textual error message.
> I believe that's supposed to indicate which SQLSTATE is generated.

Right, but we're not generating that SQLSTATE --- it's 22007 which is
"invalid datetime format". Because the datetime code is designed to
accept a rather wide variety of input formats, you could probably
quibble about which code is really more appropriate for this error and
related ones --- did the user really intend you to interpret that number
as yyyymmdd, or was his typo more basic? (If there are additional
fields following that you haven't looked at yet, it's not exactly
cut-and-dried IMHO.) Still, there are cases like '2002-11-31' where
"datetime field overflow" is clearly more correct.

The datetime code is set up in a way that doesn't really allow any easy
distinction to be made, though --- IIRC, DecodeDateTime returns -1 to
the guy who actually does the ereport(), and this would cover situations
including bad syntax, field out of range, and maybe other problems.

The simplest fix might be to make the parse/decode subroutines return
different negative numbers for the cases we need to distinguish (looks
like there are at least three relevant SQLSTATEs in the spec). Anyone
feel like working on that?

regards, tom lane