Entering date and time data

The datetime, smalldatetime, bigdatetime and bigtime 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, bigdatetime, bigtime 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. If you omit the date portion of a bigdatetime a default value of January 1, 0001 will be added.

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

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

The time component of a bigdatetime or bigtime value must be specified as follows:

hours[:minutes[:seconds[.microseconds]] [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. Microseconds must be expressed with a decimal point.

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

For bigdatetime and bigtime the value displays reflects a microsecond value. bigdatetime and bigtime have default display formats that accomodate their increased precision.

• hh:mm:ss.zzzzzzAM or PM

• hh:mm:ss.zzzzzz

• mon dd yyyy hh:mm:ss.zzzzzzAM(PM)

• mon dd yyyy hh:mm:ss.zzzzzz

• yyyy-mm-dd hh:mm:ss.zzzzzz

The format for time must be specified as:

• hours[:minutes[:seconds[.microseconds]] [AM | PM]
  

• hours[:minutes[:seconds[number of milliseconds]] [AM | PM]
  

Use 12 AM for midnight and 12 PM for noon. A bigtime value must contain either a colon or an AM or PM signifier. AM or PM can be entered in uppercase, lowercase, or mixed case.

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

To store a bigdatetime or bigtime time value that includes microseconds, specify a string literal using a point. “00:00:00.1” means one tenth of a second past midnight and “00:00:00.000001” means one millionth of a second past midnight. Any value after the colon specifying fractional seconds will continue to refer to a number of milliseconds. Such as “00:00:00:5” means 5 milliseconds.

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

You can do some arithmetic calculations on date and time datatypes values with the built-in date functions. See Transact-SQL Users Guide.