From 1a908a00b05c9f8f94cebde2551eee50329f38f2 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 16 Nov 2003 20:29:16 +0000 Subject: 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. --- src/test/regress/expected/date.out | 677 +++++++++++++++++++++++++++++++++++++ src/test/regress/sql/date.sql | 160 +++++++++ 2 files changed, 837 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/date.out b/src/test/regress/expected/date.out index 84bdcf232b9..20ffaf188de 100644 --- a/src/test/regress/expected/date.out +++ b/src/test/regress/expected/date.out @@ -62,6 +62,683 @@ SELECT f1 AS "Three" FROM DATE_TBL 04-03-2000 (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 9e98041ce81..10bd87cadcf 100644 --- a/src/test/regress/sql/date.sql +++ b/src/test/regress/sql/date.sql @@ -28,6 +28,166 @@ SELECT f1 AS "Nine" FROM DATE_TBL WHERE f1 < '2000-01-01'; 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 -- cgit v1.2.3