ASA SQL Reference
  SQL Data Types
    Date and time data types

Using unambiguous dates and times

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.SSS

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. Adaptive Server 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 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 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 

• We recommend that you always specify the year using the four-digit format.

  For more information about Y2K compliance issues, see Year 2000 compliance.

• 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:

  • year    This year

  • month    No default

  • day    1 (useful for month fields; for example, May 1999 will be the date 1999-05-01 00:00)

  • hour, minute, second, fraction    0