date_order option

Controls the interpretation of date formats.

Allowed values

MDY, YMD, DMY

Default

YMD

A temporary setting for the current user is established by the ODBC, JDBC, and OLE DB drivers ('ymd').

A temporary setting for the current user is established by SAP Open Client and jConnect TDS connections ('MDY').

Scope

	PUBLIC role	For current user	For other users
Allowed to set permanently?	Yes, with SET ANY PUBLIC OPTION	Yes	Yes, with SET ANY PUBLIC OPTION
Allowed to set temporarily?	Yes, with SET ANY PUBLIC OPTION	Yes (current connection only)	No

Remarks

The default order for year, month, and day corresponds to the ISO 8601 date format. For example, "06-01-07" is interpreted as January 7, 2006.

You can specify a different order for the interpretation of date parts. For example, if "06-01-07" represents June 1, 2007 then specify "MDY" for the date order.

Note If you change the default setting for date_order, you can also change the date_format and timestamp_format options to reflect this change.

Different values determine how the date 10/11/12 is interpreted:

Date order	Interpretation
MDY	October 11 2012
YMD	November 12 2010
DMY	November 10 2012

Use the nearest_century option to control the interpretation of two-digit years in string-to-date conversions.

The following SQL query returns 2010-11-12 using the default date_order and nearest_century settings.

SELECT CAST( CAST( '10/11/12' AS DATE ) AS VARCHAR(15) );