Entering date and time data

The datetime and smalldatetime datatypes consist of a date portion either followed by or preceded by a time portion. (You can omit either the date or the time, or both.) The date datatype has only a date and the time datatype has only the time. You must enclose values in single or double quotes.

Dates consist of a month, day, and year and can be entered in a variety of formats for date, datetime, and smalldatetime:

• You can enter the entire date as an unseparated string of 4, 6, or 8 digits, or use slash (/), hyphen (-), or period (.) separators between the date parts.

  • When entering dates as unseparated strings, use the appropriate format for that string length. Use leading zeros for single-digit years, months, and days. Dates entered in the wrong format may be misinterpreted or result in errors.

  • When entering dates with separators, use the set dateformat option to determine the expected order of date parts. If the first date part in a separated string is four digits, Adaptive Server interprets the string as yyyy-mm-dd format.

• Some date formats accept 2-digit years (yy):

  • Numbers less than 50 are interpreted as 20yy. For example, 01 is 2001, 32 is 2032, and 49 is 2049.

  • Numbers equal to or greater than 50 are interpreted as 19yy. For example, 50 is 1950, 74 is 1974, and 99 is 1999.

• You can specify the month as either a number or a name. Month names and their abbreviations are language-specific and can be entered in uppercase, lowercase, or mixed case.

• If you omit the date portion of a datetime or smalldatetime value, Adaptive Server uses the default date of January 1, 1900.

The time component of a datetime, smalldatetime, or time value must be specified as follows:

hours[:minutes[:seconds[:milliseconds]] [AM | PM]

• Use 12AM for midnight and 12PM for noon.

• A time value must contain either a colon or an AM or PM signifier. The AM or PM can be entered in uppercase, lowercase, or mixed case.

• The seconds specification can include either a decimal portion preceded by a decimal point, or a number of milliseconds preceded by a colon. For example, “15:30:20:1” means twenty seconds and one millisecond past 3:30 PM; “15:30:20.1” means twenty and one-tenth of a second past 3:30 PM.

• If you omit the time portion of a datetime or smalldatetime value, Adaptive Server uses the default time of 12:00:00:000AM.

The display format for time values is “hh:mm:ss:mmmAM” (or “PM”); for example, “10:23:40:022PM.

Use the like keyword to look for dates that match a particular pattern. If you use the equality operator (=) to search date or time values for a particular month, day, and year, Adaptive Server returns only those values for which the time is precisely 12:00:00:000AM.

For example, if you insert the value “9:20” into a column named arrival_time, Adaptive Server converts the entry into “Jan 1 1900 9:20AM.” If you look for this entry using the equality operator, it is not found:

where arrival_time = "9:20" /* does not match */

You can find the entry using the like operator:

where arrival_time like "%9:20%"

When using like, Adaptive Server first converts the dates to datetime or date format and then to varchar. The display format consists of the 3-character month in the current language, 2 characters for the day, 4 characters for the year, the time in hours and minutes, and “AM” or “PM.”