Home

	Expand Search

Transact-SQL User's Guide

Chapter 8 Adding, Changing, and Deleting Data

Datatype entry rules

Datatype entry rules

Several of the Adaptive Server-supplied datatypes have special rules for entering and searching for data. For more information on datatypes, see Chapter 7, "Creating Databases and Tables."

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

All character, text, and datetime data must be enclosed in single or double quotes when you enter it as a literal. Use single quotes if the quoted_identifier option of the set command is set on. If you use double quotes, Adaptive Server treats the text as an identifier.

Character literals may be any length, whatever the logical page size of the database. If the literal is wider than 16 kilobytes (16384 bytes), Adaptive Server treats it as text data, which has restrictive rules regarding implicit and explicit conversion to other datatypes. See the Reference Manual for a discussion of the different behavior of character and text datatypes.

When you insert character data into a char, nchar, unichar, univarchar, varchar, or nvarchar column whose specified length is less than the length of the data, the entry is truncated. Set the string_rtruncation option on to receive a warning message when this occurs.

This truncation rule applies to all character data, whether it resides in a column, a variable, or a literal string.

There are two ways to specify literal quotes within a character entry:

• Use two quotes. For example, if you begin a character entry with a single quote and you want to include a single quote as part of the entry, use two single quotes: 'I don't 'understand.'' For double quotes: "He said, ""It's not really confusing."""

• Enclose the quoted material in the opposite kind of quotation mark. In other words, surround an entry containing a double quote with single quotes, or vice versa. For example: 'George said, "There must be a better way." '

To enter a character string that is longer than the width of your screen, enter a backslash (\) before going to the next line.

Use the like keyword and wildcard characters described in Chapter 2, "Queries: Selecting Data from a Table," to search for character, text, and datetime data.

See the Reference Manual for details on inserting text data and information about trailing blanks in character data.

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.

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 

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.

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.

binary, varbinary, and image

When you enter binary, varbinary, or image data as literals, you must precede the data by "0x". For example, to enter "FF", type "0xFF". Do not, however, enclose data beginning with "0x" with quotation marks.

Binary literals may be any length, whatever the logical page size of the database. If the length of the literal is less than 16 kilobytes (16384 bytes), Adaptive Server treats the literal as varbinary data. If the length of the literal is greater than 16 kilobytes, Adaptive Server treats it as image data. See the Reference Manual for the different behaviors of binary datatypes and image datatypes.

When you insert binary data into a column whose specified length is less than the length of the data, the entry is truncated without warning.

A length of 10 for a binary or varbinary column means 10 bytes, each storing 2 hexadecimal digits.

When you create a default on a binary or varbinary column, precede it with "0x".

See the Reference Manual for information on trailing zeros in hexadecimal values.

money and smallmoney

Monetary values entered with the E notation are interpreted as float. This may cause an entry to be rejected or to lose some of its precision when it is stored as a money or smallmoney value.

money and smallmoney values can be entered with or without a preceding currency symbol such as the dollar sign ($), yen sign (¥) or pound sterling sign (£). To enter a negative value, place the minus sign after the currency symbol. Do not include commas in your entry.

You cannot enter money or smallmoney values with commas, although the default print format for money or smallmoney data places a comma after every three digits. When money or smallmoney values are displayed, they are rounded up to the nearest cent. All the arithmetic operations except modulo are available with money.

float, real, and double precision

You enter the approximate numeric types--float, real, and double precision--as a mantissa followed by an optional exponent. The mantissa can include a positive or negative sign and a decimal point. The exponent, which begins after the character "e" or "E", can include a sign but not a decimal point.

To evaluate approximate numeric data, Adaptive Server multiplies the mantissa by 10 raised to the given exponent. Table 8-1 shows examples of float, real, and double precision data:

The column's binary precision determines the maximum number of binary digits allowed in the mantissa. For float columns, you can specify a precision of up to 48 digits; for real and double precision columns, the precision is machine-dependent. If a value exceeds the column's binary precision, Adaptive Server flags the entry as an error.

decimal and numeric

The exact numeric types--dec, decimal, and numeric--begin with an optional positive or negative sign and can include a decimal point. The value of exact numeric data depends on the column's decimal precision and scale, which you specify using this syntax:

datatype [(precision  [, scale ])]

Adaptive Server treats each combination of precision and scale as a distinct datatype. For example, numeric (10,0) and numeric (5,0) are two separate datatypes. The precision and scale determine the range of values that can be stored in a decimal or numeric column:

• The precision specifies the maximum number of decimal digits that can be stored in the column. It includes all digits to the right and left of the decimal point. You can specify a precision ranging from 1 to 38 digits or use the default precision of 18 digits.

• The scale specifies the maximum number of digits that can be stored to the right of the decimal point. The scale must be less than or equal to the precision. You can specify a scale ranging from 0 to 38 digits or use the default scale of 0 digits.

If a value exceeds the column's precision or scale, Adaptive Server flags the entry as an error. Here are some examples of valid dec and numeric data:

The following entries result in errors because they exceed the column's precision or scale:

int, smallint, and tinyint

You can insert numeric values into int, smallint, and tinyint columns with the E notation, as described in the preceding section.

timestamp

You cannot insert data into a timestamp column. You must either insert an explicit null by typing "NULL" in the column or use an implicit null by providing a column list that skips the timestamp column. Adaptive Server updates the timestamp value after each insert or update. See "Inserting data into specific columns" for more information.