Reference Manual
CHAPTER 7. SQL Data Types
For storing dates and times.
Sending dates and times to the databaseDates and times may be sent to the database in one of the following ways:
Using any interface, as a string
Using ODBC, as a TIMESTAMP structure
Using Embedded SQL, as a SQLDATETIME structure
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:
As a string of format yyyy/mm/dd or yyyy-mm-dd, which is interpreted unambiguously by the database
As a string interpreted according to the DATE_ORDER database option
Unambiguous dates and timesDates 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 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.
As the year 2000 approaches it might be a good idea to specify the year using the four-digit format. This will ensure the integrity of the data into the next millenium.
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
Retrieving dates and times from the databaseDates and times may be retrieved from the database in one of the following ways:
Using any interface, as a string
Using ODBC, as a TIMESTAMP structure
Using embedded SQL, as a SQLDATETIME structure
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 that deals with dates and times, see Date and time functions. The following arithmetic operators are allowed on dates:
timestamp + integer Add the specified number of days to a date or timestamp.
timestamp - integer Subtract the specified number of days from a date or timestamp.
date - date Compute the number of days between two dates or timestamps.
date + time Create a timestamp combining the given date and time.
Date and time comparisonsThe 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.
DATE data type [Date and Time]A calendar date, such as a year, month and day.
DATETIME
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.
SQL/92 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.
DATETIME data type [Date and Time]A user-defined data type, implemented as TIMESTAMP.
SMALLDATETIME
DATETIME is provided primarily for compatibility with Adaptive Server Enterprise.
SQL/92 Vendor extension.
Sybase Compatible with Adaptive Server Enterprise.
SMALLDATETIME data type [Date and Time]A user-defined data type, implemented as TIMESTAMP.
SMALLDATETIME
SMALLDATETIME is provided primarily for compatibility with Adaptive Server Enterprise.
SQL/92 Vendor extension.
Sybase Compatible with Adaptive Server Enterprise.
TIME data type [Date and Time]The time of day, containing hour, minute, second and fraction of a second.
TIME
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.)
SQL/92 Vendor extension.
Sybase Not supported by Adaptive Server Enterprise.
TIMESTAMP data type [Date and Time]The point in time, containing year, month, day, hour, minute, second and fraction of a second.
TIMESTAMP
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.
SQL/92 Vendor extension.
Sybase Not supported in Adaptive Server Enterprise.