Transact-SQL User's Guide

Home

char, nchar, unichar, univarchar, varchar, nvarchar,
and text

Transact-SQL User's Guide

Chapter 8 Adding, Changing, and Deleting Data

Datatype entry rules

datetime and smalldatetime

Display and entry formats for datetime data provide a wide range of date output formats, and recognize a variety of input formats. The display and entry formats are controlled separately. The default display format provides output that looks like "Apr 15 1997 10:23PM". The convert command provides options to display seconds and milliseconds and to display the date with other date-part ordering. See Chapter 10, "Using the Built-In Functions in Queries," for more information on displaying date values.

Adaptive Server recognizes a wide variety of data entry formats for dates. Case is always ignored, and spaces can occur anywhere between date parts. When you enter datetime and smalldatetime values, always enclose them in single or double quotes. Use single quotes if the quoted_identifier option is on; if you use double quotes, Adaptive Server treats the entry as an identifier.

Adaptive Server recognizes the two date and time portions of the data separately, so the time can precede or follow the date. Either portion can be omitted, in which case Adaptive Server uses the default. The default date and time is January 1, 1900, 12:00:00:000AM.

For datetime, the earliest date you can use is January 1, 1753; the latest is December 31, 9999. For smalldatetime, the earliest date you can use is January 1, 1900; the latest is June 6, 2079. Dates earlier or later than these dates must be entered, stored, and manipulated as char, or unichar; or varchar or univarchar values. Adaptive Server rejects all values it cannot recognize as dates between those ranges.

Entering times

The order of time components is significant for the time portion of the data. First, enter the hours; then minutes; then seconds; then milliseconds; then AM (or am) or PM (pm). 12AM is midnight; 12PM is noon. To be recognized as time, a value must contain either a colon or an AM or PM signifier. smalldatetime is accurate only to the minute.

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 20 and one-thousandth of a second past 12:30; "12:30:20.1" means 20 and one-tenth of a second past 12:30.

Among the acceptable formats for time data are:

14:30 
14:30[:20:999] 
14:30[:20.9] 
4am 
4 PM 
[0]4[:30:20:500]AM

Entering dates

The set dateformat command specifies the order of the date parts (month, day, and year) when dates are entered as strings of numbers with separators. set language can also affect the format for dates, depending on the default date format for the language you specify. The default language is us_english, and the default date format is mdy. See the set command in the Reference Manual for more information.

dateformat affects only the dates entered as numbers with separators, such as "4/15/90" or "20.05.88". It does not affect dates where the month is provided in alphabetic format, such as "April 15, 1990" or where there are no separators, such as "19890415".

Date formats

Adaptive Server recognizes three basic date formats, as described below. Each format must be enclosed in quotes and can be preceded or followed by a time specification, as described under "Entering times".

The month is entered in alphabetic format.
- Valid formats for specifying the date alphabetically are:
```
  Apr[il] [15][,] 1997
  Apr[il] 15[,] [19]97 
  Apr[il] 1997 [15] 
```
```
  [15] Apr[il][,] 1997 
  15 Apr[il][,] [19]97 
  15 [19]97 apr[il] 
  [15] 1997 apr[il] 
```
```
  1997 APR[IL] [15]
  1997 [15] APR[IL] 
```
- Month can be a three-character abbreviation, or the full month name, as given in the specification for the current language.
- Commas are optional.
- Case is ignored.
- If you specify only the last two digits of the year, values of less than 50 are interpreted as "20yy", and values of 50 or more are interpreted as "19yy".
- Type the century only when the day is omitted or when you need a century other than the default.
- If the day is missing, Adaptive Server defaults to the first day of the month.
- When you specify the month in alphabetic format, the dateformat setting is ignored (see the set command in the Reference Manual).
The month is entered in numeric format, in a string with a slash (/), hyphen (-), or period (.) separator.
- The month, day, and year must be specified.
- The strings must be in the form:
```
     <num> <sep> <num> <sep> <num> [ <time spec> ] 
```
  or:
```
     [ <time spec> ]  <num> <sep> <num> <sep> <num> 
```
- The interpretation of the values of the date parts depends on the dateformat setting. If the ordering does not match the setting, either the values will not be interpreted as dates, because the values are out of range, or the values will be misinterpreted. For example, "12/10/08" could be interpreted as one of six different dates, depending on the dateformat setting. See the set command in the Reference Manual for more information.
- To enter "April 15, 1997" in mdy dateformat, you can use these formats:
```
  [0]4/15/[19]97
  [0]4-15-[19]97
  [0]4.15.[19]97
```
- The other entry orders are shown below with ''/" as a separator; you can also use hyphens or periods:
```
  15/[0]4/[19]97 (dmy) 
  1997/[0]4/15 (ymd) 
  1997/15/[0]4 (ydm) 
  [0]4/[19]97/15 (myd) 
  15/[19]97/[0]4 (dym) 
```
The date is given as an unseparated four-, six-, or eight-digit string, or as an empty string, or only the time value, but no date value, is given.
- The dateformat is always ignored with this entry format.
- If four digits are given, the string is interpreted as the year, and the month is set to January, the day to the first of the month. The century cannot be omitted.
- Six- or eight-digit strings are always interpreted as ymd; the month and day must always be two digits. This format is recognized: [19]960415.
- An empty string ("") or missing date is interpreted as the base date, January 1, 1900. For example, a time value like "4:33" without a date is interpreted as "January 1, 1900, 4:33AM''.

The set datefirst command specifies the day of the week (Sunday, Monday, and so on) when weekday or dw is used with datename, and a corresponding number when used with datepart. Changing the language with set language can also affect the format for dates, depending on the default first day of the week value for the language. For the default language of us_english, the default datefirst setting is Sunday=1, Monday=2, and so on; others produce Monday=1, Tuesday=2, and so on. The default behavior can be changed on a per-session basis with set datefirst. See the set command in the Reference Manual for more information.

Searching for dates and times

You can use the like keyword and wildcard characters with datetime and smalldatetime data as well as with char, unichar, nchar, varchar, univarchar, nvarchar, and text. When you use like with datetime or smalldatetime values, Adaptive Server first converts the dates to the standard datetime format and then converts them to varchar or univarchar. Since the standard display format does not include seconds or milliseconds, you cannot search for seconds or milliseconds with like and a match pattern. Use the type conversion function, convert, to search for seconds and milliseconds.

It is a good idea to use like when you search for datetime or smalldatetime values, because datetime or smalldatetime entries may contain a variety of date parts. For example, if you insert the value "9:20" into a column named arrival_time, the following clause would not find it because Adaptive Server converts the entry to "Jan 1, 1900 9:20AM":

where arrival_time = "9:20"

However, this clause would find it:

where arrival_time like "%9:20%"

If you are using like, and the day of the month is less than 10, you must insert two spaces between the month and 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", will find all dates from May 20 through May 29, but not May 2. You do not need to insert the extra space with other date comparisons, only with like, since the datetime values are converted to varchar only for the like comparison.


	Expand Search