Date and time data consist of alphanumeric data representing a date and time of day. The default display format for dates is "Mon dd yyyy hh:mmAM" (or PM), such as "Apr 15 1996 10:23AM". When time data is entered, the order of time components is significant: hours; minutes; seconds; milliseconds; AM, am, PM, or pm (12AM is midnight, 12PM is noon). To be recognized as time, a value must contain either a colon (:) or an AM/PM signifier.
Always enclose datetime values with single quotation marks ('). Capitalization (case) is ignored, and spaces can occur anywhere between date parts. SQL Server recognizes the date portion and the time portion of the data separately, so the time can precede or follow the date.
Use date functions with the CONVERT function to display seconds and milliseconds and to get other date styles and date part orderings by converting datetime data to char.
These are the date and time datatypes:
Data values for datetime range from January 1, 1753, to December 31, 9999, to an accuracy of one three-hundredth second, or 3.33 milliseconds. SQL Server rejects all values it cannot recognize as dates between 1753 and 9999. You can omit either portion, but if you omit both, datetime defaults to January 1, 1900, 12:00:00:000AM. If you omit the time portion of a datetime value, the default (12:00:00:000AM) is supplied. If you omit the date portion, the default (Jan 1 1900) is supplied. For example, an empty string or missing date is interpreted as the base date, January 1, 1900. A time value without a date, for example 4:33, is interpreted as January 1, 1900, 4:33AM.
SQL Server recognizes the following formats (alphabetic, numeric, and unseparated string) for date data. Enclose each format with single quotation marks (').
The month can be the full name or the abbreviation given in the current language; commas are optional and capitalization (case) is ignored.
These alphabetic formats are acceptable:
Apr[il] [15][,] 1996 Apr[il] 15[,] [19]96 Apr[il] 1996 [15] Apr[il] [19]96 15 [15] Apr[il][,] 1996 15 Apr[il][,][19]96 15 [19]96 apr[il] [15] 1996 apr[il] 1996 APR[IL] [15] [19]96 APR[IL] 15 1996 [15] APR[IL]
If you specify only the last two digits of the year, values less than 50 are interpreted as 20yy, and values greater than or equal to 50 are interpreted as 19yy. For example, if you specify 3, the result is 2003. If you specify 82, the result is 1982. You must type the century when the day is omitted or when you need a century other than the default.
If the day is missing, the first day of the month is supplied.
The DATEFORMAT session setting is not applied when you specify the month in alphabetic form. For more information, see the SET statement.
You must specify the month, day, and year in a string with slashes (/), hyphens (-), or periods (.) as separators.
This string must appear in the following form:
<num> <sep> <num> <sep> <num> [<time_spec>] [<time_spec>]
These numeric formats are acceptable:
[0]4/15/[19]96 (mdy) [0]4-15-[19]96 (mdy) [0]4.15.[19]96 (mdy) [04]/[19]96/15 (myd) 15/[0]4/[19]96 (dmy) 15/[19]96/[0]4 (dym) [19]96/15/[0]4 (ydm) [19]96/[04]/15 (ymd)
When the language is set to us_english, the default order for the date is mdy. You can change the date order with the SET DATEFORMAT statement, which can also affect the date order, depending on the language.
The setting for DATEFORMAT determines how date values are interpreted. If the order doesn't match the setting, the values are not interpreted as dates (because they are out of range), or the values are misinterpreted. For example, 12/10/08 can be interpreted as one of six dates, depending on the DATEFORMAT setting. For more information, see the SET statement.
An unseparated string can specify four, six, or eight digits, an empty string, or a time value without a date value.
The DATEFORMAT session setting does not apply to all-numeric date entries (numeric entries without separators). Six- or eight-digit strings are always interpreted as ymd. The month and day must always be two digits.
This is the acceptable unseparated string format:
[19]960415
A string of only four digits is interpreted as the year. The month and date are set to January 1. When specifying only four digits, you must include the century.
SQL Server recognizes the following formats for time data. Enclose each format with single quotation marks ('). The hour can refer to 12-hour or 24-hour time. Capitalization (case) (for AM or PM) is ignored.
14:30 14:30[:20:999] 14:30[:20.9] 4am 4 PM [0]4[:30:20:500]AM
Milliseconds can be preceded by either a colon (:) or a period (.). If preceded by a colon, the number means thousandths-of-a-second. If preceded by a period, a single digit means tenths-of-a-second, two digits mean hundredths-of-a-second, and three digits mean thousandths-of-a-second. For example, "12:30:20:1" means twenty and one-thousandth seconds past 12:30; "12:30:20.1" means twenty and one-tenth seconds past 12:30.
You can perform some arithmetic calculations on datetime data with the built-in date functions. For more information, see the Functions topic.
Searching for datetime values with an equal sign (=) and the month, day, and year returns only those time values that are precisely 12:00:00:000 AM (the default).
If you use the keyword LIKE with datetime values, SQL Server first converts the dates to datetime format and then to varchar. Since the standard display formats don't include seconds or milliseconds, you cannot search for them with LIKE and a matching pattern, unless you use the CONVERT function with the style parameter set to 9 or 109. However, LIKE works well when searching for datetime values that contain a variety of date parts. For example, if you insert the value 9:20 into a column named arrival_time, the clause WHERE arrival_time = '9:20', does not find the value because SQL Server converts the entry into Jan 1 1900 9:20AM. However, the clause WHERE arrival_time LIKE '%9:20%' does find the value.
When using LIKE with a single-digit day of the month (1 to 9), you must insert one space between the month and the day to match the varchar conversion of the datetime value. Similarly, if the hour is less than 10, the conversion places two spaces between the year and the hour. The clause 'LIKE May 2%' (with one space between May and 2) finds all dates from May 20 through May 29, but not May 2. You don't need to insert the extra space with other date comparisons, because LIKE is the comparison for which the datetime values are converted to varchar.