On 4/3/07, Bruce Momjian <[EMAIL PROTECTED]> wrote:
Because this patch was not completed, I have added it to the TODO list: * Fix to_date()-related functions to consistently issue errors http://archives.postgresql.org/pgsql-hackers/2007-02/msg00915.php
I'm now taking another run at this issue. Here's what I've got in mind. There are three distinct conventions for specifying a date that we consider in Postgres. These are * Julian day, * ISO week date, and * Standard Gregorian. Within an ISO week date, you can identify a date using either * year, week and day-of-week, or * year and day-of-year. Likewise within a Gregorian date, you can identify a date using * year, month and day-of-month, * year, month, week-of-month and day-of-week (extremely weird, but there it is) * year, week, and day-of-week, or * year and day-of-year. Chad Wagner mentioned that Oracle will allow a combination of Julian and Gregorian formats so long as both formats yield the same date. If we're going to stick with the theme of imitating Oracle, I propose the following: * No mixing of Gregorian and ISO fields permitted. If the format string contains both Gregorian and ISO normative fields in any sequence or combination, we throw an ERRCODE_INVALID_DATETIME_FORMAT and reject the query. * Either Gregorian or ISO format strings may include a Julian date field, as long as the results are in agreement. If the results disagree, we reject the query. * Purely non-normative fields (like "Q") are completely and silently disregarded. * A Gregorian or ISO format may be over-constraining as long as all values are in agreement. If there are any conflicts we reject the query. So, for example, we would reject something like "YYYY-IDDD" out of hand because it combines the ISO and Gregorian conventions, making it impossible to ascertain what the user really wants to do. We would allow YYYY-MM-DD J as long as the result for the YYYY-MM-DD part matches the result for the J part. We would also allow something like YYYY-MM-DD D as long as the results of YYYY-MM-DD and D matched. So to_date('2007-07-18 4', 'YYYY-MM-DD D') would successfully return the date 18 July 2007, but if you tried to_date('2007-07-18 5', 'YYYY-MM-DD D') you would get an error. If there are no objections I'd be happy to cook a patch up. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend