User's Guide Part VI. SQL Anywhere Reference Chapter 41. SQL Anywhere Data TypesFor storing dates and times.
Date and time data types
DATE
| TIME
| TIMESTAMP
DATE A calendar date, such as a year, month and day. The year can be from the year 0001 to 9999. For historical reasons, a DATE column can also contain an hour and minute, but the TIMESTAMP data type is now recommended for anything with hours and minutes. A DATE value requires 4 bytes of storage.
TIME time of day, containing hour, minute, second and fraction of a second. The fraction is stored to 6 decimal places. A TIME value requires 8 bytes of storage. (ODBC standards restrict TIME data type to an accuracy of seconds. For this reason you should not use TIME data types in WHERE clause comparisons that rely on a higher accuracy than seconds.)
TIMESTAMP point in time, containing year, month, day, hour, minute, second and fraction of a second. The fraction is stored to 6 decimal places. A TIMESTAMP value requires 8 bytes of storage.
Although the range of possible dates for the TIMESTAMP data type is the same as the DATE type (covering years 0001 to 9999), the useful range of TIMESTAMP date types is from 1600-02-28 23:59:59 to 7911-01-01 00:00:00. Prior to, and after this range the time portion of the TIMESTAMP may be incomplete.
Dates and times may be sent to the database in one of the following ways:
When a time is sent to the database as a string (for the TIME data type) or as part of a string (for TIMESTAMP or DATE data types), the hours, minutes, and seconds must be separated by colons in the format hh:mm:ss.sss, but can appear anywhere in the string. The following are valid and unambiguous strings for specifying times:
21:35 -- 24 hour clock if no am or pm specified
10:00pm -- pm specified, so interpreted as 12 hour clock
10:00 -- 10:00am in the absence of pm
10:23:32.234 -- seconds and fractions of a second included
When a date is sent to the database as a string, conversion to a date is automatic. The string can be supplied in one of two ways:
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. SQL Anywhere 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 SQL Anywhere 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, with 2 digit years defaulting to the 20th century. 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.
With an appropriate setting of DATE_ORDER, the following strings are all valid dates:
92-05-23 21:35
92/5/23
1992/05/23
May 23 1992
23-May-1992
Tuesday May 23, 1992 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 1992' will be the date '1992-05-01 00:00' )
hour, minute, second, fraction 0
Dates and times may be retrieved from the database in one of the following ways:
When a date or time is retrieved as a string, it is retrieved in the format specified by the database options DATE_FORMAT, TIME_FORMAT and TIMESTAMP_FORMAT. For descriptions of these options, see "SET OPTION statement".
For information on functions dealing with dates and times, see "Date and time functions". The following arithmetic operators are allowed on dates:
The DATE data type also contains a time. If the time is not specified when a date is entered into the database, the time defaults to 0:00 or 12:00am (midnight). Any date comparisons always involve the times as well. A database date value of '1992-05-23 10:00' will not be equal to the constant '1992-05-23'. The DATEFORMAT function or one of the other date functions can be used to compare parts of a date and time field. For example:
DATEFORMAT(invoice_date,'yyyy/mm/dd') = '1992/05/23'
If a database column requires only a date, client applications should ensure that times are not specified when data is entered into the database. This way, comparisons with date-only strings will work as expected.
If you wish to compare a date to a string as a string , you must use the DATEFORMAT function or CAST function to convert the date to a string before comparing.