Unambiguous Dates and Times

Usage

Dates in the format yyyy/mm/dd or yyyy-mm-dd are always recognized as dates regardless of the DATE_ORDER setting. You can use other characters as separators; for example, a question mark, a space character, or a comma. You should use this format in any context where different users might 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.

A string of the form hh:mm:ss.sss is also 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 following form is an unambiguous date-time value:

YYYY-MM-DD HH.MM.SS.SSSSSS

You can use periods in the time only in combination with a date.

In other contexts, you can use a more flexible date format. Sybase IQ can interpret a wide range of strings as formats. The interpretation depends on the setting of the DATE_ORDER database option. The DATE_ORDER database option can have the value ‘MDY’, ‘YMD’, or ‘DMY’. For example, to set the DATE_ORDER option to ‘DMY’ enter:

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. Use the SET OPTION statement to change the value.

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.

You can supply the year as either 2 or 4 digits. The value of the NEAREST_CENTURY option [TSQL] 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.

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: