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 “/” or “-”; for example, “?”, a space character, or “,”. 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.sss 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. Also, the form
YYYY-MM-DD HH.MM.SS.SSSSSS
is an unambiguous date-time value. Periods can be used in the time only in combination with a date.
In other contexts, a more flexible date format can be used. Sybase IQ can interpret a wide range of strings as formats. 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 OPTION statement.
The database option DATE_ORDER determines whether the string 10/11/12 is interpreted by the database as Oct 11 1912, Nov 12 1910, or Nov 10 1912. The year, month, and day of a date string should be separated by some character (for example /, -, 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 option NEAREST_CENTURY 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. Thus, by default, 50 is interpreted as 1950 and 49 is interpreted as 2049. For more information, see the “NEAREST_CENTURY option [TSQL]”.
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.
Sybase recommends that you always specify the year using the 4-digit format.
With an appropriate setting of DATE_ORDER, the following strings are all valid dates:
99-05-23 21:35 99/5/23 1999/05/23 May 23 1999 23-May-1999 Tuesday May 23, 1999 10:00pm
If a string contains only a partial date specification, default values are used to fill out the date. The following defaults are used:
day 1 (useful for month fields; for example, 'May 1999' is the date '1999-05-01 00:00')
