Dates in the format yyyy/mm/dd or yyyy-mm-dd are always recognized unambiguously as dates, regardless of the date_order setting. Other characters can be used as separators instead of a forward slash (/) or a hyphen (-); for example, a question mark (?), a space character, or a comma (,). You should use this format in any context where different users may be employing different date_order settings. For example, in stored procedures, use of the unambiguous date format prevents misinterpretation of dates according to the user's date_order setting.

Also, a string of the form hh:mm:ss.ssssss is interpreted unambiguously as a time.

For combinations of dates and times, any unambiguous date and any unambiguous time yield an unambiguous date-time value. The form yyyy-mm-ddThh:mm:ss.ssssss is an unambiguous date-time value. The date-time separator, T, can be omitted giving the unambiguous form yyyy-mm-dd hh:mm:ss.ssssss. Periods can be used instead of colons giving the unambiguous form yyyy-mm-dd hh.mm.ss.ssssss. Periods can be used in the time but only in combination with a date or the date-time separator, T (Thh.mm.ss.ssssss).

In other contexts, a more flexible date format can be used. SQL Anywhere can interpret a wide range of strings as dates. The interpretation depends on the setting of the database option date_order. The date_order database option can have the value MDY, YMD, or DMY See SET OPTION statement.

For example, the following statement sets the date_order option to DMY:

SET OPTION date_order = 'DMY' ;

The default date_order setting is YMD. The ODBC driver sets the date_order option to YMD whenever a connection is made. The value can still be changed using the SET TEMPORARY OPTION statement.

The database option date_order determines whether the string 10/11/12 is interpreted by the database as November 12, 2010; October 11, 2012; or November 10, 2012. The year, month, and day of a date string should be separated by some character (/, -, or space) and appear in the order specified by the date_order option.

The year can be supplied as either 2 or 4 digits. The value of the nearest_century option affects the interpretation of 2-digit years: 2000 is added to values less than nearest_century and 1900 is added to all other values. The default value of this option is 50. So, by default, 50 is interpreted as 1950 and 49 is interpreted 2049.

The month can be the name or number of the month. The hours and minutes are separated by a colon, but can appear anywhere in the string.

 Notes