Controls the interpretation of date formats.
MDY, YMD, DMY
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').
|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|
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.
Different values determine how the date 10/11/12 is interpreted:
|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) );