Apply (a somewhat revised version of) Greg Mullane's patch to eliminate

heuristic determination of day vs month in date/time input.  Add the
ability to specify that input is interpreted as yy-mm-dd order (which
formerly worked, but only for yy greater than 31).  DateStyle's input
component now has the preferred spellings DMY, MDY, or YMD; the older
keywords European and US are now aliases for the first two of these.
Per recent discussions on pgsql-general.

18 files changed, 320 insertions, 225 deletions

diff --git a/src/backend/commands/variable.c b/src/backend/commands/variable.c
index a9fc94adc3d..e0b041636e6 100644
--- a/src/backend/commands/variable.c
+++ b/src/backend/commands/variable.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/commands/variable.c,v 1.84 2003/07/28 00:09:14 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/commands/variable.c,v 1.85 2003/07/29 00:03:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -51,10 +51,10 @@ const char *
 assign_datestyle(const char *value, bool doit, bool interactive)
 {
 	int			newDateStyle = DateStyle;
-	bool		newEuroDates = EuroDates;
+	int			newDateOrder = DateOrder;
 	bool		ok = true;
-	int			dcnt = 0,
-				ecnt = 0;
+	int			scnt = 0,
+				ocnt = 0;
 	char	   *rawstring;
 	char	   *result;
 	List	   *elemlist;
@@ -85,38 +85,43 @@ assign_datestyle(const char *value, bool doit, bool interactive)
 		if (strcasecmp(tok, "ISO") == 0)
 		{
 			newDateStyle = USE_ISO_DATES;
-			dcnt++;
+			scnt++;
 		}
 		else if (strcasecmp(tok, "SQL") == 0)
 		{
 			newDateStyle = USE_SQL_DATES;
-			dcnt++;
+			scnt++;
 		}
 		else if (strncasecmp(tok, "POSTGRES", 8) == 0)
 		{
 			newDateStyle = USE_POSTGRES_DATES;
-			dcnt++;
+			scnt++;
 		}
 		else if (strcasecmp(tok, "GERMAN") == 0)
 		{
 			newDateStyle = USE_GERMAN_DATES;
-			dcnt++;
-			if ((ecnt > 0) && (!newEuroDates))
-				ok = false;
-			newEuroDates = TRUE;
+			scnt++;
+			/* GERMAN also sets DMY, unless explicitly overridden */
+			if (ocnt == 0)
+				newDateOrder = DATEORDER_DMY;
 		}
-		else if (strncasecmp(tok, "EURO", 4) == 0)
+		else if (strcasecmp(tok, "YMD") == 0)
 		{
-			newEuroDates = TRUE;
-			ecnt++;
+			newDateOrder = DATEORDER_YMD;
+			ocnt++;
 		}
-		else if (strcasecmp(tok, "US") == 0
-				 || strncasecmp(tok, "NONEURO", 7) == 0)
+		else if (strcasecmp(tok, "DMY") == 0 ||
+				 strncasecmp(tok, "EURO", 4) == 0)
 		{
-			newEuroDates = FALSE;
-			ecnt++;
-			if ((dcnt > 0) && (newDateStyle == USE_GERMAN_DATES))
-				ok = false;
+			newDateOrder = DATEORDER_DMY;
+			ocnt++;
+		}
+		else if (strcasecmp(tok, "MDY") == 0 ||
+				 strcasecmp(tok, "US") == 0 ||
+				 strncasecmp(tok, "NONEURO", 7) == 0)
+		{
+			newDateOrder = DATEORDER_MDY;
+			ocnt++;
 		}
 		else if (strcasecmp(tok, "DEFAULT") == 0)
 		{
@@ -128,15 +133,17 @@ assign_datestyle(const char *value, bool doit, bool interactive)
 			 * to handle constructs like "DEFAULT, ISO".
 			 */
 			int			saveDateStyle = DateStyle;
-			bool		saveEuroDates = EuroDates;
+			int			saveDateOrder = DateOrder;
 			const char *subval;
 
 			subval = assign_datestyle(GetConfigOptionResetString("datestyle"),
 									  true, interactive);
-			newDateStyle = DateStyle;
-			newEuroDates = EuroDates;
+			if (scnt == 0)
+				newDateStyle = DateStyle;
+			if (ocnt == 0)
+				newDateOrder = DateOrder;
 			DateStyle = saveDateStyle;
-			EuroDates = saveEuroDates;
+			DateOrder = saveDateOrder;
 			if (!subval)
 			{
 				ok = false;
@@ -145,8 +152,6 @@ assign_datestyle(const char *value, bool doit, bool interactive)
 			/* Here we know that our own return value is always malloc'd */
 			/* when doit is true */
 			free((char *) subval);
-			dcnt++;
-			ecnt++;
 		}
 		else
 		{
@@ -160,7 +165,7 @@ assign_datestyle(const char *value, bool doit, bool interactive)
 		}
 	}
 
-	if (dcnt > 1 || ecnt > 1)
+	if (scnt > 1 || ocnt > 1)
 		ok = false;
 
 	pfree(rawstring);
@@ -203,14 +208,25 @@ assign_datestyle(const char *value, bool doit, bool interactive)
 			strcpy(result, "Postgres");
 			break;
 	}
-	strcat(result, newEuroDates ? ", European" : ", US");
+	switch (newDateOrder)
+	{
+		case DATEORDER_YMD:
+			strcat(result, ", YMD");
+			break;
+		case DATEORDER_DMY:
+			strcat(result, ", DMY");
+			break;
+		default:
+			strcat(result, ", MDY");
+			break;
+	}
 
 	/*
 	 * Finally, it's safe to assign to the global variables; the
 	 * assignment cannot fail now.
 	 */
 	DateStyle = newDateStyle;
-	EuroDates = newEuroDates;
+	DateOrder = newDateOrder;
 
 	return result;
 }
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 9b2324b220f..80f2be70ed6 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/tcop/postgres.c,v 1.352 2003/07/27 21:49:54 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/tcop/postgres.c,v 1.353 2003/07/29 00:03:18 tgl Exp $
  *
  * NOTES
  *	  this is the "main" module of the postgres backend and
@@ -1958,7 +1958,7 @@ usage(char *progname)
 	printf("  -c NAME=VALUE   set run-time parameter\n");
 	printf("  -d 0-5          debugging level (0 is off)\n");
 	printf("  -D DATADIR      database directory\n");
-	printf("  -e              use European date format\n");
+	printf("  -e              use European date input format (DMY)\n");
 	printf("  -E              echo query before execution\n");
 	printf("  -F              turn fsync off\n");
 	printf("  -N              do not use newline as interactive query delimiter\n");
@@ -2155,7 +2155,7 @@ PostgresMain(int argc, char *argv[], const char *username)
 			case 'e':
 
 				/*
-				 * Use european date formats.
+				 * Use European date input format (DMY)
 				 */
 				SetConfigOption("datestyle", "euro", ctx, gucsource);
 				break;
@@ -2626,7 +2626,7 @@ PostgresMain(int argc, char *argv[], const char *username)
 	if (!IsUnderPostmaster)
 	{
 		puts("\nPOSTGRES backend interactive interface ");
-		puts("$Revision: 1.352 $ $Date: 2003/07/27 21:49:54 $\n");
+		puts("$Revision: 1.353 $ $Date: 2003/07/29 00:03:18 $\n");
 	}
 
 	/*
diff --git a/src/backend/utils/adt/datetime.c b/src/backend/utils/adt/datetime.c
index 1b9b4fcc821..f053c9ebb2c 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.107 2003/07/27 04:53:04 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/adt/datetime.c,v 1.108 2003/07/29 00:03:18 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -1425,23 +1425,47 @@ DecodeDateTime(char **field, int *ftype, int nf,
 		fmask |= tmask;
 	}
 
-	/* there is no year zero in AD/BC notation; i.e. "1 BC" == year 0 */
-	if (bc)
+	if (fmask & DTK_M(YEAR))
 	{
-		if (tm->tm_year > 0)
-			tm->tm_year = -(tm->tm_year - 1);
-		else
-			ereport(ERROR,
-					(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
-					 errmsg("inconsistent use of year %04d and \"BC\"",
-							tm->tm_year)));
+		/* there is no year zero in AD/BC notation; i.e. "1 BC" == year 0 */
+		if (bc)
+		{
+			if (tm->tm_year > 0)
+				tm->tm_year = -(tm->tm_year - 1);
+			else
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_DATETIME_FORMAT),
+						 errmsg("inconsistent use of year %04d and \"BC\"",
+								tm->tm_year)));
+		}
+		else if (is2digits)
+		{
+			if (tm->tm_year < 70)
+				tm->tm_year += 2000;
+			else if (tm->tm_year < 100)
+				tm->tm_year += 1900;
+		}
 	}
-	else if (is2digits)
+
+	/* now that we have correct year, decode DOY */
+	if (fmask & DTK_M(DOY))
 	{
-		if (tm->tm_year < 70)
-			tm->tm_year += 2000;
-		else if (tm->tm_year < 100)
-			tm->tm_year += 1900;
+		j2date(date2j(tm->tm_year, 1, 1) + tm->tm_yday - 1,
+			   &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+	}
+
+	/* check for valid month */
+	if (fmask & DTK_M(MONTH))
+	{
+		if (tm->tm_mon < 1 || tm->tm_mon > 12)
+			return -1;
+	}
+
+	/* minimal check for valid day */
+	if (fmask & DTK_M(DAY))
+	{
+		if (tm->tm_mday < 1 || tm->tm_mday > 31)
+			return -1;
 	}
 
 	if ((mer != HR24) && (tm->tm_hour > 12))
@@ -1461,13 +1485,11 @@ DecodeDateTime(char **field, int *ftype, int nf,
 		 * check for valid day of month, now that we know for sure the
 		 * month and year...
 		 */
-		if ((tm->tm_mday < 1)
-		 || (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1]))
+		if (tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
 			return -1;
 
 		/* timezone not specified? then find local timezone if possible */
-		if (((fmask & DTK_DATE_M) == DTK_DATE_M)
-			&& (tzp != NULL) && (!(fmask & DTK_M(TZ))))
+		if ((tzp != NULL) && (!(fmask & DTK_M(TZ))))
 		{
 			/*
 			 * daylight savings time modifier but no standard timezone?
@@ -2259,6 +2281,22 @@ DecodeDate(char *str, int fmask, int *tmask, struct tm * tm)
 			tm->tm_year += 1900;
 	}
 
+	/* now that we have correct year, decode DOY */
+	if (fmask & DTK_M(DOY))
+	{
+		j2date(date2j(tm->tm_year, 1, 1) + tm->tm_yday - 1,
+			   &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+	}
+
+	/* check for valid month */
+	if (tm->tm_mon < 1 || tm->tm_mon > 12)
+		return -1;
+
+	/* check for valid day */
+	if (tm->tm_mday < 1 ||
+		tm->tm_mday > day_tab[isleap(tm->tm_year)][tm->tm_mon - 1])
+		return -1;
+
 	return 0;
 }	/* DecodeDate() */
 
@@ -2356,8 +2394,8 @@ DecodeNumber(int flen, char *str, int fmask,
 	if (*cp == '.')
 	{
 		/*
-		 * More than two digits? Then could be a date or a run-together
-		 * time: 2001.360 20011225 040506.789
+		 * More than two digits before decimal point? Then could be a date
+		 * or a run-together time: 2001.360 20011225 040506.789
 		 */
 		if ((cp - str) > 2)
 			return DecodeNumberField(flen, str, (fmask | DTK_DATE_M),
@@ -2370,85 +2408,91 @@ DecodeNumber(int flen, char *str, int fmask,
 	else if (*cp != '\0')
 		return -1;
 
-	/* Special case day of year? */
-	if ((flen == 3) && (fmask & DTK_M(YEAR))
-		&& ((val >= 1) && (val <= 366)))
+	/* Special case for day of year */
+	if ((flen == 3) &&
+		((fmask & DTK_DATE_M) == DTK_M(YEAR)) &&
+		((val >= 1) && (val <= 366)))
 	{
 		*tmask = (DTK_M(DOY) | DTK_M(MONTH) | DTK_M(DAY));
 		tm->tm_yday = val;
-		j2date(date2j(tm->tm_year, 1, 1) + tm->tm_yday - 1,
-			   &tm->tm_year, &tm->tm_mon, &tm->tm_mday);
+		/* tm_mon and tm_mday can't actually be set yet ... */
+		return 0;
 	}
 
-	/***
-	 * Enough digits to be unequivocal year? Used to test for 4 digits or
-	 * more, but we now test first for a three-digit doy so anything
-	 * bigger than two digits had better be an explicit year.
-	 * - thomas 1999-01-09
-	 * Back to requiring a 4 digit year. We accept a two digit
-	 * year farther down. - thomas 2000-03-28
-	 ***/
-	else if (flen >= 4)
+	/* Switch based on what we have so far */
+	switch (fmask & DTK_DATE_M)
 	{
-		*tmask = DTK_M(YEAR);
+		case 0:
+			/*
+			 * Nothing so far; make a decision about what we think the
+			 * input is.  There used to be lots of heuristics here, but
+			 * the consensus now is to be paranoid.  It *must* be either
+			 * YYYY-MM-DD (with a more-than-two-digit year field), or the
+			 * field order defined by DateOrder.
+			 */
+			if (flen >= 3 || DateOrder == DATEORDER_YMD)
+			{
+				*tmask = DTK_M(YEAR);
+				tm->tm_year = val;
+			}
+			else if (DateOrder == DATEORDER_DMY)
+			{
+				*tmask = DTK_M(DAY);
+				tm->tm_mday = val;
+			}
+			else
+			{
+				*tmask = DTK_M(MONTH);
+				tm->tm_mon = val;
+			}
+			break;
 
-		/* already have a year? then see if we can substitute... */
-		if ((fmask & DTK_M(YEAR)) && (!(fmask & DTK_M(DAY)))
-			&& ((tm->tm_year >= 1) && (tm->tm_year <= 31)))
-		{
-			tm->tm_mday = tm->tm_year;
+		case (DTK_M(YEAR)):
+			/* Must be at second field of YY-MM-DD */
+			*tmask = DTK_M(MONTH);
+			tm->tm_mon = 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;
+			break;
 
-		tm->tm_year = val;
-	}
+		case (DTK_M(DAY)):
+			/* Must be at second field of DD-MM-YY */
+			*tmask = DTK_M(MONTH);
+			tm->tm_mon = val;
+			break;
 
-	/* already have year? then could be month */
-	else if ((fmask & DTK_M(YEAR)) && (!(fmask & DTK_M(MONTH)))
-			 && ((val >= 1) && (val <= 12)))
-	{
-		*tmask = DTK_M(MONTH);
-		tm->tm_mon = val;
-	}
-	/* no year and EuroDates enabled? then could be day */
-	else if ((EuroDates || (fmask & DTK_M(MONTH)))
-			 && (!(fmask & DTK_M(YEAR)) && !(fmask & DTK_M(DAY)))
-			 && ((val >= 1) && (val <= 31)))
-	{
-		*tmask = DTK_M(DAY);
-		tm->tm_mday = val;
-	}
-	else if ((!(fmask & DTK_M(MONTH)))
-			 && ((val >= 1) && (val <= 12)))
-	{
-		*tmask = DTK_M(MONTH);
-		tm->tm_mon = val;
-	}
-	else if ((!(fmask & DTK_M(DAY)))
-			 && ((val >= 1) && (val <= 31)))
-	{
-		*tmask = DTK_M(DAY);
-		tm->tm_mday = val;
+		case (DTK_M(MONTH) | DTK_M(DAY)):
+			/* Must be at third field of DD-MM-YY or MM-DD-YY */
+			*tmask = DTK_M(YEAR);
+			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;
+
+		default:
+			/* Anything else is bogus input */
+			return -1;
 	}
 
 	/*
-	 * Check for 2 or 4 or more digits, but currently we reach here only
-	 * if two digits. - thomas 2000-03-28
+	 * When processing a year field, mark it for adjustment if it's
+	 * exactly two digits.
 	 */
-	else if (!(fmask & DTK_M(YEAR))
-			 && ((flen >= 4) || (flen == 2)))
+	if (*tmask == DTK_M(YEAR))
 	{
-		*tmask = DTK_M(YEAR);
-		tm->tm_year = val;
-
-		/* adjust ONLY if exactly two digits... */
 		*is2digits = (flen == 2);
 	}
-	else
-		return -1;
 
 	return 0;
-}	/* DecodeNumber() */
+}
 
 
 /* DecodeNumberField()
@@ -2514,18 +2558,6 @@ DecodeNumberField(int len, char *str, int fmask,
 
 			return DTK_DATE;
 		}
-		/* yyddd? */
-		else if (len == 5)
-		{
-			*tmask = DTK_DATE_M;
-			tm->tm_mday = atoi(str + 2);
-			*(str + 2) = '\0';
-			tm->tm_mon = 1;
-			tm->tm_year = atoi(str + 0);
-			*is2digits = TRUE;
-
-			return DTK_DATE;
-		}
 	}
 
 	/* not all time fields are specified? */
@@ -3152,7 +3184,7 @@ EncodeDateOnly(struct tm * tm, int style, char *str)
 
 		case USE_SQL_DATES:
 			/* compatible with Oracle/Ingres date formats */
-			if (EuroDates)
+			if (DateOrder == DATEORDER_DMY)
 				sprintf(str, "%02d/%02d", tm->tm_mday, tm->tm_mon);
 			else
 				sprintf(str, "%02d/%02d", tm->tm_mon, tm->tm_mday);
@@ -3174,7 +3206,7 @@ EncodeDateOnly(struct tm * tm, int style, char *str)
 		case USE_POSTGRES_DATES:
 		default:
 			/* traditional date-only style for Postgres */
-			if (EuroDates)
+			if (DateOrder == DATEORDER_DMY)
 				sprintf(str, "%02d-%02d", tm->tm_mday, tm->tm_mon);
 			else
 				sprintf(str, "%02d-%02d", tm->tm_mon, tm->tm_mday);
@@ -3308,7 +3340,7 @@ EncodeDateTime(struct tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, cha
 		case USE_SQL_DATES:
 			/* Compatible with Oracle/Ingres date formats */
 
-			if (EuroDates)
+			if (DateOrder == DATEORDER_DMY)
 				sprintf(str, "%02d/%02d", tm->tm_mday, tm->tm_mon);
 			else
 				sprintf(str, "%02d/%02d", tm->tm_mon, tm->tm_mday);
@@ -3410,7 +3442,7 @@ EncodeDateTime(struct tm * tm, fsec_t fsec, int *tzp, char **tzn, int style, cha
 			strncpy(str, days[tm->tm_wday], 3);
 			strcpy((str + 3), " ");
 
-			if (EuroDates)
+			if (DateOrder == DATEORDER_DMY)
 				sprintf((str + 4), "%02d %3s", tm->tm_mday, months[tm->tm_mon - 1]);
 			else
 				sprintf((str + 4), "%3s %02d", months[tm->tm_mon - 1], tm->tm_mday);
diff --git a/src/backend/utils/init/globals.c b/src/backend/utils/init/globals.c
index 2a7a372fa6d..6e6551f1019 100644
--- a/src/backend/utils/init/globals.c
+++ b/src/backend/utils/init/globals.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/utils/init/globals.c,v 1.72 2003/06/27 14:45:30 petere Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/init/globals.c,v 1.73 2003/07/29 00:03:18 tgl Exp $
  *
  * NOTES
  *	  Globals used all over the place should be declared here and not
@@ -62,7 +62,7 @@ bool		IsUnderPostmaster = false;
 bool		ExitOnAnyError = false;
 
 int			DateStyle = USE_ISO_DATES;
-bool		EuroDates = false;
+int			DateOrder = DATEORDER_MDY;
 bool		HasCTZSet = false;
 int			CTimeZone = 0;
 
diff --git a/src/backend/utils/misc/README b/src/backend/utils/misc/README
index fe252d534de..b8610a97b8e 100644
--- a/src/backend/utils/misc/README
+++ b/src/backend/utils/misc/README
@@ -1,4 +1,4 @@
-$Header: /cvsroot/pgsql/src/backend/utils/misc/README,v 1.1 2002/05/17 01:19:18 tgl Exp $
+$Header: /cvsroot/pgsql/src/backend/utils/misc/README,v 1.2 2003/07/29 00:03:18 tgl Exp $
 
 
 GUC IMPLEMENTATION NOTES
@@ -49,8 +49,8 @@ variables, but the return value is handled differently:
 	malloc'd (not palloc'd!!!) string --- assign that value instead
 The third choice is allowed in case the assign_hook wants to return a
 "canonical" version of the new value.  For example, the assign_hook for
-datestyle always returns a string that includes both basic datestyle and
-us/euro option, although the input might have specified only one.
+datestyle always returns a string that includes both output and input
+datestyle options, although the input might have specified only one.
 
 If a show_hook is provided, it points to a function of the signature
 	const char *show_hook(void)
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index d6ed9ba4626..a7eb4f5ed16 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -10,7 +10,7 @@
  * Written by Peter Eisentraut <peter_e@gmx.net>.
  *
  * IDENTIFICATION
- *	  $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.143 2003/07/28 19:31:32 tgl Exp $
+ *	  $Header: /cvsroot/pgsql/src/backend/utils/misc/guc.c,v 1.144 2003/07/29 00:03:18 tgl Exp $
  *
  *--------------------------------------------------------------------
  */
@@ -1338,13 +1338,13 @@ static struct config_string ConfigureNamesString[] =
 
 	{
 		{"DateStyle", PGC_USERSET, CLIENT_CONN_LOCALE,
-			gettext_noop("The display format for date and time values"),
-			gettext_noop("As well as the rules for interpreting ambiguous date "
-						 "input values"),
+			gettext_noop("The display format for date and time values, "),
+			gettext_noop("as well as the rules for interpreting ambiguous "
+						 "date input values"),
 			GUC_LIST_INPUT | GUC_REPORT
 		},
 		&datestyle_string,
-		"ISO, US", assign_datestyle, NULL
+		"ISO, MDY", assign_datestyle, NULL
 	},
 
 	{
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 584e24f4287..6ed1aae2081 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -212,7 +212,7 @@
 
 # - Locale and Formatting -
 
-#datestyle = 'iso, us'
+#datestyle = 'iso, mdy'
 #timezone = unknown		# actually, defaults to TZ environment setting
 #australian_timezones = false
 #extra_float_digits = 0		# min -15, max 2
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 8f70c25e761..a7e9fbca3d4 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3,7 +3,7 @@
  *
  * Copyright 2000-2002 by PostgreSQL Global Development Group
  *
- * $Header: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v 1.81 2003/06/30 16:47:01 tgl Exp $
+ * $Header: /cvsroot/pgsql/src/bin/psql/tab-complete.c,v 1.82 2003/07/29 00:03:18 tgl Exp $
  */
 
 /*----------------------------------------------------------------------
@@ -1187,8 +1187,10 @@ psql_completion(char *text, int start, int end)
 	{
 		if (strcasecmp(prev2_wd, "DateStyle") == 0)
 		{
-			char	   *my_list[] = {"'ISO'", "'SQL'", "'Postgres'",
-			"'European'", "'NonEuropean'", "'German'", "DEFAULT", NULL};
+			char	   *my_list[] = {"ISO", "SQL", "Postgres", "German",
+									 "YMD", "DMY", "MDY",
+									 "US", "European", "NonEuropean",
+									 "DEFAULT", NULL};
 
 			COMPLETE_WITH_LIST(my_list);
 		}
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 5fee7fc25f9..7e7219ef4dc 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -12,7 +12,7 @@
  * Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
  * Portions Copyright (c) 1994, Regents of the University of California
  *
- * $Id: miscadmin.h,v 1.128 2003/07/27 21:49:54 tgl Exp $
+ * $Id: miscadmin.h,v 1.129 2003/07/29 00:03:18 tgl Exp $
  *
  * NOTES
  *	  some of the information in this file should be moved to
@@ -135,35 +135,53 @@ extern char pg_pathname[];
  */
 extern DLLIMPORT Oid MyDatabaseId;
 
-/* Date/Time Configuration
+/*
+ * Date/Time Configuration
  *
- * Constants to pass info from runtime environment:
- *	USE_POSTGRES_DATES specifies traditional postgres format for output.
- *	USE_ISO_DATES specifies ISO-compliant format for output.
- *	USE_SQL_DATES specified Oracle/Ingres-compliant format for output.
- *	USE_GERMAN_DATES specifies German-style dd.mm/yyyy date format.
+ * DateStyle defines the output formatting choice for date/time types:
+ *	USE_POSTGRES_DATES specifies traditional Postgres format
+ *	USE_ISO_DATES specifies ISO-compliant format
+ *	USE_SQL_DATES specifies Oracle/Ingres-compliant format
+ *	USE_GERMAN_DATES specifies German-style dd.mm/yyyy
  *
- * DateStyle specifies preference for date formatting for output.
- * EuroDates if client prefers dates interpreted and written w/European conventions.
+ * DateOrder defines the field order to be assumed when reading an
+ * ambiguous date (anything not in YYYY-MM-DD format, with a four-digit
+ * year field first, is taken to be ambiguous):
+ *	DATEORDER_YMD specifies field order yy-mm-dd
+ *	DATEORDER_DMY specifies field order dd-mm-yy ("European" convention)
+ *	DATEORDER_MDY specifies field order mm-dd-yy ("US" convention)
  *
- * HasCTZSet is true if user has set timezone as a numeric offset from UTC.
- * If so, CTimeZone is the timezone offset in seconds (using the Unix-ish
- * sign convention, ie, positive offset is west of UTC, rather than the
- * SQL-ish convention that positive is east of UTC).
+ * In the Postgres and SQL DateStyles, DateOrder also selects output field
+ * order: day comes before month in DMY style, else month comes before day.
+ *
+ * The user-visible "DateStyle" run-time parameter subsumes both of these.
  */
 
-#define MAXTZLEN		10		/* max TZ name len, not counting tr. null */
-
+/* valid DateStyle values */
 #define USE_POSTGRES_DATES		0
 #define USE_ISO_DATES			1
 #define USE_SQL_DATES			2
 #define USE_GERMAN_DATES		3
 
+/* valid DateOrder values */
+#define DATEORDER_YMD			0
+#define DATEORDER_DMY			1
+#define DATEORDER_MDY			2
+
 extern int	DateStyle;
-extern bool EuroDates;
+extern int	DateOrder;
+
+/*
+ * HasCTZSet is true if user has set timezone as a numeric offset from UTC.
+ * If so, CTimeZone is the timezone offset in seconds (using the Unix-ish
+ * sign convention, ie, positive offset is west of UTC, rather than the
+ * SQL-ish convention that positive is east of UTC).
+ */
 extern bool HasCTZSet;
 extern int	CTimeZone;
 
+#define MAXTZLEN		10		/* max TZ name len, not counting tr. null */
+
 extern bool enableFsync;
 extern bool allowSystemTableMods;
 extern DLLIMPORT int SortMem;
diff --git a/src/test/regress/expected/horology-no-DST-before-1970.out b/src/test/regress/expected/horology-no-DST-before-1970.out
index f824122b876..0aa1da407f4 100644
--- a/src/test/regress/expected/horology-no-DST-before-1970.out
+++ b/src/test/regress/expected/horology-no-DST-before-1970.out
@@ -3,7 +3,7 @@
 --
 -- needed so tests pass even in Australia
 SET australian_timezones = 'off';
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 --
 -- Test various input formats
 --
@@ -79,12 +79,17 @@ SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
  Thu Dec 27 04:05:06.789 2001 PST
 (1 row)
 
+-- should fail in mdy mode:
+SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
+ERROR:  invalid input syntax for timestamp with time zone: "27/12/2001 04:05:06.789-08"
+set datestyle to dmy;
 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
            timestamptz            
 ----------------------------------
- Thu Dec 27 04:05:06.789 2001 PST
+ Thu 27 Dec 04:05:06.789 2001 PST
 (1 row)
 
+reset datestyle;
 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
            timestamptz            
 ----------------------------------
@@ -170,13 +175,13 @@ SELECT timestamp with time zone 'J2452271T040506.789-08';
 (1 row)
 
 -- German/European-style dates with periods as delimiters
-SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
            timestamptz            
 ----------------------------------
  Wed Dec 26 12:05:06.789 2001 PST
 (1 row)
 
-SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
            timestamptz            
 ----------------------------------
  Thu Dec 27 04:05:06.789 2001 PST
@@ -257,7 +262,7 @@ SELECT time with time zone 'T040506.789 -08';
  04:05:06.7890-08
 (1 row)
 
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 --
 -- date, time arithmetic
 --
@@ -2385,9 +2390,9 @@ DROP TABLE TEMP_TIMESTAMP;
 --
 SET DateStyle TO 'US,Postgres';
 SHOW DateStyle;
-  DateStyle   
---------------
- Postgres, US
+   DateStyle   
+---------------
+ Postgres, MDY
 (1 row)
 
 SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL;
@@ -2557,7 +2562,7 @@ SET DateStyle TO 'US,SQL';
 SHOW DateStyle;
  DateStyle 
 -----------
- SQL, US
+ SQL, MDY
 (1 row)
 
 SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL;
@@ -2643,9 +2648,9 @@ SELECT '' AS seven, f1 AS us_sql FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,Postgres';
 SHOW DateStyle;
-     DateStyle      
---------------------
- Postgres, European
+   DateStyle   
+---------------
+ Postgres, DMY
 (1 row)
 
 INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
@@ -2739,9 +2744,9 @@ SELECT '' AS seven, f1 AS european_postgres FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,ISO';
 SHOW DateStyle;
-   DateStyle   
----------------
- ISO, European
+ DateStyle 
+-----------
+ ISO, DMY
 (1 row)
 
 SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL;
@@ -2828,9 +2833,9 @@ SELECT '' AS seven, f1 AS european_iso FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,SQL';
 SHOW DateStyle;
-   DateStyle   
----------------
- SQL, European
+ DateStyle 
+-----------
+ SQL, DMY
 (1 row)
 
 SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL;
diff --git a/src/test/regress/expected/horology-solaris-1947.out b/src/test/regress/expected/horology-solaris-1947.out
index 736208001b5..cdb9c7f1d86 100644
--- a/src/test/regress/expected/horology-solaris-1947.out
+++ b/src/test/regress/expected/horology-solaris-1947.out
@@ -3,7 +3,7 @@
 --
 -- needed so tests pass even in Australia
 SET australian_timezones = 'off';
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 --
 -- Test various input formats
 --
@@ -79,12 +79,17 @@ SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
  Thu Dec 27 04:05:06.789 2001 PST
 (1 row)
 
+-- should fail in mdy mode:
+SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
+ERROR:  invalid input syntax for timestamp with time zone: "27/12/2001 04:05:06.789-08"
+set datestyle to dmy;
 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
            timestamptz            
 ----------------------------------
- Thu Dec 27 04:05:06.789 2001 PST
+ Thu 27 Dec 04:05:06.789 2001 PST
 (1 row)
 
+reset datestyle;
 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
            timestamptz            
 ----------------------------------
@@ -170,13 +175,13 @@ SELECT timestamp with time zone 'J2452271T040506.789-08';
 (1 row)
 
 -- German/European-style dates with periods as delimiters
-SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
            timestamptz            
 ----------------------------------
  Wed Dec 26 12:05:06.789 2001 PST
 (1 row)
 
-SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
            timestamptz            
 ----------------------------------
  Thu Dec 27 04:05:06.789 2001 PST
@@ -257,7 +262,7 @@ SELECT time with time zone 'T040506.789 -08';
  04:05:06.7890-08
 (1 row)
 
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 --
 -- date, time arithmetic
 --
@@ -2385,9 +2390,9 @@ DROP TABLE TEMP_TIMESTAMP;
 --
 SET DateStyle TO 'US,Postgres';
 SHOW DateStyle;
-  DateStyle   
---------------
- Postgres, US
+   DateStyle   
+---------------
+ Postgres, MDY
 (1 row)
 
 SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL;
@@ -2557,7 +2562,7 @@ SET DateStyle TO 'US,SQL';
 SHOW DateStyle;
  DateStyle 
 -----------
- SQL, US
+ SQL, MDY
 (1 row)
 
 SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL;
@@ -2643,9 +2648,9 @@ SELECT '' AS seven, f1 AS us_sql FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,Postgres';
 SHOW DateStyle;
-     DateStyle      
---------------------
- Postgres, European
+   DateStyle   
+---------------
+ Postgres, DMY
 (1 row)
 
 INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
@@ -2739,9 +2744,9 @@ SELECT '' AS seven, f1 AS european_postgres FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,ISO';
 SHOW DateStyle;
-   DateStyle   
----------------
- ISO, European
+ DateStyle 
+-----------
+ ISO, DMY
 (1 row)
 
 SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL;
@@ -2828,9 +2833,9 @@ SELECT '' AS seven, f1 AS european_iso FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,SQL';
 SHOW DateStyle;
-   DateStyle   
----------------
- SQL, European
+ DateStyle 
+-----------
+ SQL, DMY
 (1 row)
 
 SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL;
diff --git a/src/test/regress/expected/horology.out b/src/test/regress/expected/horology.out
index 735e703e4c4..984e75e8b4c 100644
--- a/src/test/regress/expected/horology.out
+++ b/src/test/regress/expected/horology.out
@@ -3,7 +3,7 @@
 --
 -- needed so tests pass even in Australia
 SET australian_timezones = 'off';
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 --
 -- Test various input formats
 --
@@ -79,12 +79,17 @@ SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
  Thu Dec 27 04:05:06.789 2001 PST
 (1 row)
 
+-- should fail in mdy mode:
+SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
+ERROR:  invalid input syntax for timestamp with time zone: "27/12/2001 04:05:06.789-08"
+set datestyle to dmy;
 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
            timestamptz            
 ----------------------------------
- Thu Dec 27 04:05:06.789 2001 PST
+ Thu 27 Dec 04:05:06.789 2001 PST
 (1 row)
 
+reset datestyle;
 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
            timestamptz            
 ----------------------------------
@@ -170,13 +175,13 @@ SELECT timestamp with time zone 'J2452271T040506.789-08';
 (1 row)
 
 -- German/European-style dates with periods as delimiters
-SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
            timestamptz            
 ----------------------------------
  Wed Dec 26 12:05:06.789 2001 PST
 (1 row)
 
-SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
            timestamptz            
 ----------------------------------
  Thu Dec 27 04:05:06.789 2001 PST
@@ -257,7 +262,7 @@ SELECT time with time zone 'T040506.789 -08';
  04:05:06.7890-08
 (1 row)
 
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 --
 -- date, time arithmetic
 --
@@ -2385,9 +2390,9 @@ DROP TABLE TEMP_TIMESTAMP;
 --
 SET DateStyle TO 'US,Postgres';
 SHOW DateStyle;
-  DateStyle   
---------------
- Postgres, US
+   DateStyle   
+---------------
+ Postgres, MDY
 (1 row)
 
 SELECT '' AS "64", d1 AS us_postgres FROM TIMESTAMP_TBL;
@@ -2557,7 +2562,7 @@ SET DateStyle TO 'US,SQL';
 SHOW DateStyle;
  DateStyle 
 -----------
- SQL, US
+ SQL, MDY
 (1 row)
 
 SELECT '' AS "64", d1 AS us_sql FROM TIMESTAMP_TBL;
@@ -2643,9 +2648,9 @@ SELECT '' AS seven, f1 AS us_sql FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,Postgres';
 SHOW DateStyle;
-     DateStyle      
---------------------
- Postgres, European
+   DateStyle   
+---------------
+ Postgres, DMY
 (1 row)
 
 INSERT INTO TIMESTAMP_TBL VALUES('13/06/1957');
@@ -2739,9 +2744,9 @@ SELECT '' AS seven, f1 AS european_postgres FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,ISO';
 SHOW DateStyle;
-   DateStyle   
----------------
- ISO, European
+ DateStyle 
+-----------
+ ISO, DMY
 (1 row)
 
 SELECT '' AS "65", d1 AS european_iso FROM TIMESTAMP_TBL;
@@ -2828,9 +2833,9 @@ SELECT '' AS seven, f1 AS european_iso FROM ABSTIME_TBL;
 
 SET DateStyle TO 'European,SQL';
 SHOW DateStyle;
-   DateStyle   
----------------
- SQL, European
+ DateStyle 
+-----------
+ SQL, DMY
 (1 row)
 
 SELECT '' AS "65", d1 AS european_sql FROM TIMESTAMP_TBL;
diff --git a/src/test/regress/expected/timestamp.out b/src/test/regress/expected/timestamp.out
index 71420dd2baf..0b45bb9c1a7 100644
--- a/src/test/regress/expected/timestamp.out
+++ b/src/test/regress/expected/timestamp.out
@@ -97,9 +97,11 @@ INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 PST');
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST');
+set datestyle to ymd;
 INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
 INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
-INSERT INTO TIMESTAMP_TBL VALUES ('97.041 17:32:01 UTC');
+reset datestyle;
+INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC');
 -- Check date conversion and date arithmetic
 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb 10 17:32:01 1997');
diff --git a/src/test/regress/expected/timestamptz.out b/src/test/regress/expected/timestamptz.out
index a383ac2f616..49525518c7b 100644
--- a/src/test/regress/expected/timestamptz.out
+++ b/src/test/regress/expected/timestamptz.out
@@ -92,9 +92,11 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 PST');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('02-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 PST');
+set datestyle to ymd;
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('97.041 17:32:01 UTC');
+reset datestyle;
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC');
 -- Check date conversion and date arithmetic
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb 10 17:32:01 1997');
diff --git a/src/test/regress/pg_regress.sh b/src/test/regress/pg_regress.sh
index 037d6f83ec4..230d2befe1f 100644
--- a/src/test/regress/pg_regress.sh
+++ b/src/test/regress/pg_regress.sh
@@ -1,5 +1,5 @@
 #! /bin/sh
-# $Header: /cvsroot/pgsql/src/test/regress/Attic/pg_regress.sh,v 1.31 2003/05/14 03:26:03 tgl Exp $
+# $Header: /cvsroot/pgsql/src/test/regress/Attic/pg_regress.sh,v 1.32 2003/07/29 00:03:19 tgl Exp $
 
 me=`basename $0`
 : ${TMPDIR=/tmp}
@@ -191,7 +191,7 @@ esac
 # ----------
 
 PGTZ='PST8PDT'; export PGTZ
-PGDATESTYLE='ISO,US'; export PGDATESTYLE
+PGDATESTYLE='ISO, MDY'; export PGDATESTYLE
 
 
 # ----------
@@ -432,7 +432,7 @@ PSQL="$bindir/psql -q -X $psql_options"
 # ----------
 
 PGTZ='PST8PDT'; export PGTZ
-PGDATESTYLE='Postgres,US'; export PGDATESTYLE
+PGDATESTYLE='Postgres, MDY'; export PGDATESTYLE
 
 
 # ----------
diff --git a/src/test/regress/sql/horology.sql b/src/test/regress/sql/horology.sql
index 88ac83b5add..c9171b28bdd 100644
--- a/src/test/regress/sql/horology.sql
+++ b/src/test/regress/sql/horology.sql
@@ -3,7 +3,7 @@
 --
 -- needed so tests pass even in Australia
 SET australian_timezones = 'off';
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 
 --
 -- Test various input formats
@@ -20,7 +20,11 @@ SELECT timestamp with time zone '2001-12-27 04:05:06.789-08';
 SELECT timestamp with time zone '2001.12.27 04:05:06.789-08';
 SELECT timestamp with time zone '2001/12/27 04:05:06.789-08';
 SELECT timestamp with time zone '12/27/2001 04:05:06.789-08';
+-- should fail in mdy mode:
 SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
+set datestyle to dmy;
+SELECT timestamp with time zone '27/12/2001 04:05:06.789-08';
+reset datestyle;
 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789+08';
 SELECT timestamp with time zone 'Y2001M12D27H04M05S06.789-08';
 SELECT timestamp with time zone 'Y2001M12D27H04MM05S06.789+08';
@@ -36,8 +40,8 @@ SELECT timestamp with time zone 'J2452271T040506-08';
 SELECT timestamp with time zone 'J2452271T040506.789+08';
 SELECT timestamp with time zone 'J2452271T040506.789-08';
 -- German/European-style dates with periods as delimiters
-SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
-SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789+08';
+SELECT timestamp with time zone '12.27.2001 04:05:06.789-08';
 SET DateStyle = 'German';
 SELECT timestamp with time zone '27.12.2001 04:05:06.789+08';
 SELECT timestamp with time zone '27.12.2001 04:05:06.789-08';
@@ -53,7 +57,7 @@ SELECT time with time zone 'T040506.789+08';
 SELECT time with time zone 'T040506.789-08';
 SELECT time with time zone 'T040506.789 +08';
 SELECT time with time zone 'T040506.789 -08';
-SET DateStyle = 'Postgres,US';
+SET DateStyle = 'Postgres, MDY';
 
 --
 -- date, time arithmetic
diff --git a/src/test/regress/sql/timestamp.sql b/src/test/regress/sql/timestamp.sql
index b08e81e80e4..48a9151a703 100644
--- a/src/test/regress/sql/timestamp.sql
+++ b/src/test/regress/sql/timestamp.sql
@@ -80,9 +80,11 @@ INSERT INTO TIMESTAMP_TBL VALUES ('1997-02-10 17:32:01 PST');
 INSERT INTO TIMESTAMP_TBL VALUES ('Feb-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMP_TBL VALUES ('02-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMP_TBL VALUES ('19970210 173201 PST');
+set datestyle to ymd;
 INSERT INTO TIMESTAMP_TBL VALUES ('97FEB10 5:32:01PM UTC');
 INSERT INTO TIMESTAMP_TBL VALUES ('97/02/10 17:32:01 UTC');
-INSERT INTO TIMESTAMP_TBL VALUES ('97.041 17:32:01 UTC');
+reset datestyle;
+INSERT INTO TIMESTAMP_TBL VALUES ('1997.041 17:32:01 UTC');
 
 -- Check date conversion and date arithmetic
 INSERT INTO TIMESTAMP_TBL VALUES ('1997-06-10 18:32:01 PDT');
diff --git a/src/test/regress/sql/timestamptz.sql b/src/test/regress/sql/timestamptz.sql
index e60bacb9624..71414f198e1 100644
--- a/src/test/regress/sql/timestamptz.sql
+++ b/src/test/regress/sql/timestamptz.sql
@@ -74,9 +74,11 @@ INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-02-10 17:32:01 PST');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('Feb-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('02-10-1997 17:32:01 PST');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('19970210 173201 PST');
+set datestyle to ymd;
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97FEB10 5:32:01PM UTC');
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('97/02/10 17:32:01 UTC');
-INSERT INTO TIMESTAMPTZ_TBL VALUES ('97.041 17:32:01 UTC');
+reset datestyle;
+INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997.041 17:32:01 UTC');
 
 -- Check date conversion and date arithmetic
 INSERT INTO TIMESTAMPTZ_TBL VALUES ('1997-06-10 18:32:01 PDT');