From 1a908a00b05c9f8f94cebde2551eee50329f38f2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 16 Nov 2003 20:29:16 +0000 Subject: [PATCH] Fix datetime input parsing to accept YYYY-MONTHNAME-DD and related syntaxes, which had been unintentionally broken by recent changes to tighten up the DateStyle rules for all-numeric date input. Add documentation and regression tests for this, too. --- doc/src/sgml/datatype.sgml | 30 +- src/backend/utils/adt/datetime.c | 81 ++++- src/test/regress/expected/date.out | 677 +++++++++++++++++++++++++++++++++++++ src/test/regress/sql/date.sql | 160 +++++++++ 4 files changed, 928 insertions(+), 20 deletions(-) diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index fbd1437364..82ae3a22fb 100644 --- a/doc/src/sgml/datatype.sgml +++ b/doc/src/sgml/datatype.sgml @@ -1,5 +1,5 @@ @@ -1464,7 +1464,7 @@ SELECT b, char_length(b) FROM test2; 1999-01-08 - ISO 8601, January 8 in any mode + ISO 8601; January 8 in any mode (recommended format) @@ -1485,6 +1485,30 @@ SELECT b, char_length(b) FROM test2; + 1999-Jan-08 + January 8 in any mode + + + Jan-08-1999 + January 8 in any mode + + + 08-Jan-1999 + January 8 in any mode + + + 99-Jan-08 + January 8 in YMD mode, else error + + + 08-Jan-99 + January 8, except error in YMD mode + + + Jan-08-99 + January 8, except error in YMD mode + + 19990108 ISO 8601; January 8, 1999 in any mode @@ -1625,7 +1649,7 @@ SELECT b, char_length(b) FROM test2; zulu - Military abbreviation for GMT + Military abbreviation for UTC z diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c index 6471f648f3..7cc29a2d13 100644 --- a/src/backend/utils/adt/datetime.c +++ b/src/backend/utils/adt/datetime.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.118 2003/09/25 06:58:03 petere Exp $ + * $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.119 2003/11/16 20:29:16 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -25,7 +25,7 @@ #include "utils/guc.h" -static int DecodeNumber(int flen, char *field, +static int DecodeNumber(int flen, char *field, bool haveTextMonth, int fmask, int *tmask, struct tm * tm, fsec_t *fsec, int *is2digits); static int DecodeNumberField(int len, char *str, @@ -924,7 +924,7 @@ DecodeDateTime(char **field, int *ftype, int nf, int val; int dterr; int mer = HR24; - int haveTextMonth = FALSE; + bool haveTextMonth = FALSE; int is2digits = FALSE; int bc = FALSE; @@ -1281,7 +1281,8 @@ DecodeDateTime(char **field, int *ftype, int nf, /* otherwise it is a single date/time field... */ else { - dterr = DecodeNumber(flen, field[i], fmask, + dterr = DecodeNumber(flen, field[i], + haveTextMonth, fmask, &tmask, tm, fsec, &is2digits); if (dterr) @@ -2032,6 +2033,7 @@ DecodeTimeOnly(char **field, int *ftype, int nf, else { dterr = DecodeNumber(flen, field[i], + FALSE, (fmask | DTK_DATE_M), &tmask, tm, fsec, &is2digits); @@ -2229,6 +2231,7 @@ DecodeDate(char *str, int fmask, int *tmask, struct tm * tm) int i, len; int dterr; + bool haveTextMonth = FALSE; int bc = FALSE; int is2digits = FALSE; int type, @@ -2283,6 +2286,7 @@ DecodeDate(char *str, int fmask, int *tmask, struct tm * tm) { case MONTH: tm->tm_mon = val; + haveTextMonth = TRUE; break; case ADBC: @@ -2312,7 +2316,7 @@ DecodeDate(char *str, int fmask, int *tmask, struct tm * tm) if ((len = strlen(field[i])) <= 0) return DTERR_BAD_FORMAT; - dterr = DecodeNumber(len, field[i], fmask, + dterr = DecodeNumber(len, field[i], haveTextMonth, fmask, &dmask, tm, &fsec, &is2digits); if (dterr) @@ -2444,7 +2448,7 @@ DecodeTime(char *str, int fmask, int *tmask, struct tm * tm, fsec_t *fsec) * Return 0 if okay, a DTERR code if not. */ static int -DecodeNumber(int flen, char *str, int fmask, +DecodeNumber(int flen, char *str, bool haveTextMonth, int fmask, int *tmask, struct tm * tm, fsec_t *fsec, int *is2digits) { int val; @@ -2534,10 +2538,59 @@ DecodeNumber(int flen, char *str, int fmask, tm->tm_mon = val; break; + case (DTK_M(MONTH)): + if (haveTextMonth) + { + /* + * We are at the first numeric field of a date that included + * a textual month name. We want to support the variants + * MON-DD-YYYY, DD-MON-YYYY, and YYYY-MON-DD as unambiguous + * inputs. We will also accept MON-DD-YY or DD-MON-YY in + * either DMY or MDY modes, as well as YY-MON-DD in YMD mode. + */ + if (flen >= 3 || DateOrder == DATEORDER_YMD) + { + *tmask = DTK_M(YEAR); + tm->tm_year = val; + } + else + { + *tmask = DTK_M(DAY); + tm->tm_mday = val; + } + } + else + { + /* Must be at second field of MM-DD-YY */ + *tmask = DTK_M(DAY); + tm->tm_mday = val; + } + break; + case (DTK_M(YEAR) | DTK_M(MONTH)): - /* Must be at third field of YY-MM-DD */ - *tmask = DTK_M(DAY); - tm->tm_mday = val; + if (haveTextMonth) + { + /* Need to accept DD-MON-YYYY even in YMD mode */ + if (flen >= 3 && *is2digits) + { + /* Guess that first numeric field is day was wrong */ + *tmask = DTK_M(DAY); /* YEAR is already set */ + tm->tm_mday = tm->tm_year; + tm->tm_year = val; + *is2digits = FALSE; + } + else + { + *tmask = DTK_M(DAY); + tm->tm_mday = val; + } + } + else + { + /* Must be at third field of YY-MM-DD */ + *tmask = DTK_M(DAY); + tm->tm_mday = val; + } break; case (DTK_M(DAY)): @@ -2552,12 +2605,6 @@ DecodeNumber(int flen, char *str, int fmask, tm->tm_year = val; break; - case (DTK_M(MONTH)): - /* Must be at second field of MM-DD-YY */ - *tmask = DTK_M(DAY); - tm->tm_mday = val; - break; - case (DTK_M(YEAR) | DTK_M(MONTH) | DTK_M(DAY)): /* we have all the date, so it must be a time field */ dterr = DecodeNumberField(flen, str, fmask, @@ -2574,10 +2621,10 @@ DecodeNumber(int flen, char *str, int fmask, /* * When processing a year field, mark it for adjustment if it's - * exactly two digits. + * only one or two digits. */ if (*tmask == DTK_M(YEAR)) - *is2digits = (flen == 2); + *is2digits = (flen <= 2); return 0; } diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index 84bdcf232b..20ffaf188d 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -63,6 +63,683 @@ SELECT f1 AS "Three" FROM DATE_TBL (3 rows) -- +-- Check all the documented input formats +-- +SET datestyle TO iso; -- display results in ISO +SET datestyle TO ymd; +SELECT date 'January 8, 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-18'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '1/8/1999'; +ERROR: date/time field value out of range: "1/8/1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1/18/1999'; +ERROR: date/time field value out of range: "1/18/1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '18/1/1999'; +ERROR: date/time field value out of range: "18/1/1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01/02/03'; + date +------------ + 2001-02-03 +(1 row) + +SELECT date '19990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999.008'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'J2451187'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'January 8, 99 BC'; +ERROR: date/time field value out of range: "January 8, 99 BC" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '99-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-99'; +ERROR: date/time field value out of range: "08-Jan-99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08-Jan-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-99'; +ERROR: date/time field value out of range: "Jan-08-99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date 'Jan-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-Jan'; +ERROR: invalid input syntax for type date: "99-08-Jan" +SELECT date '1999-08-Jan'; +ERROR: invalid input syntax for type date: "1999-08-Jan" +SELECT date '99 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 99'; +ERROR: date/time field value out of range: "08 Jan 99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08 Jan 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 99'; +ERROR: date/time field value out of range: "Jan 08 99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date 'Jan 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-99'; +ERROR: date/time field value out of range: "08-01-99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08-01-1999'; +ERROR: date/time field value out of range: "08-01-1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01-08-99'; +ERROR: date/time field value out of range: "01-08-99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01-08-1999'; +ERROR: date/time field value out of range: "01-08-1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '99-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '1999-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 99'; +ERROR: date/time field value out of range: "08 01 99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '08 01 1999'; +ERROR: date/time field value out of range: "08 01 1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01 08 99'; +ERROR: date/time field value out of range: "01 08 99" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01 08 1999'; +ERROR: date/time field value out of range: "01 08 1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '99 08 01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '1999 08 01'; + date +------------ + 1999-08-01 +(1 row) + +SET datestyle TO dmy; +SELECT date 'January 8, 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-18'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '1/8/1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '1/18/1999'; +ERROR: date/time field value out of range: "1/18/1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '18/1/1999'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '01/02/03'; + date +------------ + 2003-02-01 +(1 row) + +SELECT date '19990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999.008'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'J2451187'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'January 8, 99 BC'; + date +--------------- + 0099-01-08 BC +(1 row) + +SELECT date '99-Jan-08'; +ERROR: date/time field value out of range: "99-Jan-08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-Jan'; +ERROR: invalid input syntax for type date: "99-08-Jan" +SELECT date '1999-08-Jan'; +ERROR: invalid input syntax for type date: "1999-08-Jan" +SELECT date '99 Jan 08'; +ERROR: date/time field value out of range: "99 Jan 08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 Jan'; +ERROR: invalid input syntax for type date: "99 08 Jan" +SELECT date '1999 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-01-08'; +ERROR: date/time field value out of range: "99-01-08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01-08-99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01-08-1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99-08-01'; +ERROR: date/time field value out of range: "99-08-01" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 01 08'; +ERROR: date/time field value out of range: "99 01 08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01 08 99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01 08 1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 08 01'; +ERROR: date/time field value out of range: "99 08 01" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 08 01'; + date +------------ + 1999-08-01 +(1 row) + +SET datestyle TO mdy; +SELECT date 'January 8, 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999-01-18'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '1/8/1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1/18/1999'; + date +------------ + 1999-01-18 +(1 row) + +SELECT date '18/1/1999'; +ERROR: date/time field value out of range: "18/1/1999" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '01/02/03'; + date +------------ + 2003-01-02 +(1 row) + +SELECT date '19990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '990108'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '1999.008'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'J2451187'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'January 8, 99 BC'; + date +--------------- + 0099-01-08 BC +(1 row) + +SELECT date '99-Jan-08'; +ERROR: date/time field value out of range: "99-Jan-08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-Jan-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-Jan-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-Jan'; +ERROR: invalid input syntax for type date: "99-08-Jan" +SELECT date '1999-08-Jan'; +ERROR: invalid input syntax for type date: "1999-08-Jan" +SELECT date '99 Jan 08'; +ERROR: invalid input syntax for type date: "99 Jan 08" +SELECT date '1999 Jan 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 Jan 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date 'Jan 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 Jan'; +ERROR: invalid input syntax for type date: "99 08 Jan" +SELECT date '1999 08 Jan'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-01-08'; +ERROR: date/time field value out of range: "99-01-08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-01-08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08-01-99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '08-01-1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01-08-99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01-08-1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99-08-01'; +ERROR: date/time field value out of range: "99-08-01" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999-08-01'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '99 01 08'; +ERROR: date/time field value out of range: "99 01 08" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 01 08'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '08 01 99'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '08 01 1999'; + date +------------ + 1999-08-01 +(1 row) + +SELECT date '01 08 99'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '01 08 1999'; + date +------------ + 1999-01-08 +(1 row) + +SELECT date '99 08 01'; +ERROR: date/time field value out of range: "99 08 01" +HINT: Perhaps you need a different "datestyle" setting. +SELECT date '1999 08 01'; + date +------------ + 1999-08-01 +(1 row) + +RESET datestyle; +-- -- Simple math -- Leave most of it for the horology tests -- diff --git a/src/test/regress/sql/date.sql b/src/test/regress/sql/date.sql index 9e98041ce8..10bd87cadc 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -29,6 +29,166 @@ SELECT f1 AS "Three" FROM DATE_TBL WHERE f1 BETWEEN '2000-01-01' AND '2001-01-01'; -- +-- Check all the documented input formats +-- +SET datestyle TO iso; -- display results in ISO + +SET datestyle TO ymd; + +SELECT date 'January 8, 1999'; +SELECT date '1999-01-08'; +SELECT date '1999-01-18'; +SELECT date '1/8/1999'; +SELECT date '1/18/1999'; +SELECT date '18/1/1999'; +SELECT date '01/02/03'; +SELECT date '19990108'; +SELECT date '990108'; +SELECT date '1999.008'; +SELECT date 'J2451187'; +SELECT date 'January 8, 99 BC'; + +SELECT date '99-Jan-08'; +SELECT date '1999-Jan-08'; +SELECT date '08-Jan-99'; +SELECT date '08-Jan-1999'; +SELECT date 'Jan-08-99'; +SELECT date 'Jan-08-1999'; +SELECT date '99-08-Jan'; +SELECT date '1999-08-Jan'; + +SELECT date '99 Jan 08'; +SELECT date '1999 Jan 08'; +SELECT date '08 Jan 99'; +SELECT date '08 Jan 1999'; +SELECT date 'Jan 08 99'; +SELECT date 'Jan 08 1999'; +SELECT date '99 08 Jan'; +SELECT date '1999 08 Jan'; + +SELECT date '99-01-08'; +SELECT date '1999-01-08'; +SELECT date '08-01-99'; +SELECT date '08-01-1999'; +SELECT date '01-08-99'; +SELECT date '01-08-1999'; +SELECT date '99-08-01'; +SELECT date '1999-08-01'; + +SELECT date '99 01 08'; +SELECT date '1999 01 08'; +SELECT date '08 01 99'; +SELECT date '08 01 1999'; +SELECT date '01 08 99'; +SELECT date '01 08 1999'; +SELECT date '99 08 01'; +SELECT date '1999 08 01'; + +SET datestyle TO dmy; + +SELECT date 'January 8, 1999'; +SELECT date '1999-01-08'; +SELECT date '1999-01-18'; +SELECT date '1/8/1999'; +SELECT date '1/18/1999'; +SELECT date '18/1/1999'; +SELECT date '01/02/03'; +SELECT date '19990108'; +SELECT date '990108'; +SELECT date '1999.008'; +SELECT date 'J2451187'; +SELECT date 'January 8, 99 BC'; + +SELECT date '99-Jan-08'; +SELECT date '1999-Jan-08'; +SELECT date '08-Jan-99'; +SELECT date '08-Jan-1999'; +SELECT date 'Jan-08-99'; +SELECT date 'Jan-08-1999'; +SELECT date '99-08-Jan'; +SELECT date '1999-08-Jan'; + +SELECT date '99 Jan 08'; +SELECT date '1999 Jan 08'; +SELECT date '08 Jan 99'; +SELECT date '08 Jan 1999'; +SELECT date 'Jan 08 99'; +SELECT date 'Jan 08 1999'; +SELECT date '99 08 Jan'; +SELECT date '1999 08 Jan'; + +SELECT date '99-01-08'; +SELECT date '1999-01-08'; +SELECT date '08-01-99'; +SELECT date '08-01-1999'; +SELECT date '01-08-99'; +SELECT date '01-08-1999'; +SELECT date '99-08-01'; +SELECT date '1999-08-01'; + +SELECT date '99 01 08'; +SELECT date '1999 01 08'; +SELECT date '08 01 99'; +SELECT date '08 01 1999'; +SELECT date '01 08 99'; +SELECT date '01 08 1999'; +SELECT date '99 08 01'; +SELECT date '1999 08 01'; + +SET datestyle TO mdy; + +SELECT date 'January 8, 1999'; +SELECT date '1999-01-08'; +SELECT date '1999-01-18'; +SELECT date '1/8/1999'; +SELECT date '1/18/1999'; +SELECT date '18/1/1999'; +SELECT date '01/02/03'; +SELECT date '19990108'; +SELECT date '990108'; +SELECT date '1999.008'; +SELECT date 'J2451187'; +SELECT date 'January 8, 99 BC'; + +SELECT date '99-Jan-08'; +SELECT date '1999-Jan-08'; +SELECT date '08-Jan-99'; +SELECT date '08-Jan-1999'; +SELECT date 'Jan-08-99'; +SELECT date 'Jan-08-1999'; +SELECT date '99-08-Jan'; +SELECT date '1999-08-Jan'; + +SELECT date '99 Jan 08'; +SELECT date '1999 Jan 08'; +SELECT date '08 Jan 99'; +SELECT date '08 Jan 1999'; +SELECT date 'Jan 08 99'; +SELECT date 'Jan 08 1999'; +SELECT date '99 08 Jan'; +SELECT date '1999 08 Jan'; + +SELECT date '99-01-08'; +SELECT date '1999-01-08'; +SELECT date '08-01-99'; +SELECT date '08-01-1999'; +SELECT date '01-08-99'; +SELECT date '01-08-1999'; +SELECT date '99-08-01'; +SELECT date '1999-08-01'; + +SELECT date '99 01 08'; +SELECT date '1999 01 08'; +SELECT date '08 01 99'; +SELECT date '08 01 1999'; +SELECT date '01 08 99'; +SELECT date '01 08 1999'; +SELECT date '99 08 01'; +SELECT date '1999 08 01'; + +RESET datestyle; + +-- -- Simple math -- Leave most of it for the horology tests -- -- 2.11.0