Datatype Conversion Guidelines and Constraints

There are general guidelines and limitations to consider for datatype conversions.

• Convert Character Data to a Noncharacter Type
  You can convert character data to a noncharacter type—such as a money, date/time, exact numeric, or approximate numeric type—if it consists entirely of characters that are valid for the new type.
• Convert from One Character Type to Another
  When converting from a multibyte character set to a single-byte character set, characters with no single-byte equivalent are converted to question marks.
• Convert Numbers to a Character Type
  You can convert exact and approximate numeric data to a character type.
• Convert to or from unitext
  You can implicitly convert to unitext from other character and binary datatypes. You can explicitly convert from unitext to other datatypes, and vice versa.
• Rounding During Conversion To and From Money Types
  The money and smallmoney types store four digits to the right of the decimal point, but rounds up to the nearest hundredth (.01) for display purposes. When data is converted to a money type, it is rounded up to four places.
• Convert Date and Time Information
  Data that is recognizable as a date can be converted to datetime, smalldatetime, date, or time. Incorrect month names lead to syntax errors. Dates that fall outside the acceptable range for the datatype lead to arithmetic overflow errors.
• Convert Between Numeric Types
  You can convert data from one numeric type to another. Errors can occur if the new type is an exact numeric with precision or scale that is insufficient to hold the data.
• Convert Between Binary and Integer Types
  binary and varbinary types store hexadecimal-like data consisting of a “0x” prefix followed by a string of digits and letters.
• Convert Between Binary and Numeric or Decimal Types
  When you convert a binary or varbinary type to numeric or decimal, you must specify the “00” or “01” values after the “0x” digit; otherwise, the conversion fails.
• Convert Image Columns to Binary Types
  You can use the convert function to convert an image column to binary or varbinary.
• Convert Other Types to bit
  Exact and approximate numeric types can be implicitly converted to the bit type. Character types require an explicit convert function.
• Convert Hexadecimal Data
  For conversion results that are reliable across platforms, use the hextoint and inttohex functions.
• Convert bigtime and bigdatetime Data
  Implicit and explicit conversions are allowed where a decreased precision results in the loss of data.
• Convert NULL Value
  You can use the convert function to change NULL to NOT NULL and NOT NULL to NULL.