Each column, local variable, expression, and parameter has a related data type. Expressions must often be converted between data types. This data type conversion will either occur implicitly or explicitly. See also General DB Datatypes.

System Data Types

The most common data types may be grouped into data groups. The examples below are typical data types for ISO, but they are very year dependent. EGs: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003. The "-->" indicates the equivalent in Microsoft SQL Server.

Explicit Data Type Conversion

T-SQL provides two functions to explicitly convert an expression from one data type to another. CAST is preferred because it is based upon the SQL-92 standard, but CONVERT has some formatting options not available via CAST.

CAST(expression AS DataType[(length)])
--EG:
CAST(@myVar As char(50))
CONVERT(DataType[(length)], expression[, style]])
--EG:
CONVERT(char(50), @myVar)

For CONVERT(), the style parameter is an integer for formatting. Here is the stuff from the MSDN Library.

For datetime or smalldatetime. ** Input when converting to datetime; output when converting to character data.

Without century
(yy)		 With century
(yyyy)
Standard
Input/Output**
- 0 or 100 Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 Europe default + milliseconds
Approximates RFC 822		 dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
- 130 Hijri dd mon yyyy hh:mi:ss:mmmAM
- 131 Hijri dd/mm/yy hh:mi:ss:mmmAM

For float or real.

Value Output
0 (default) Six digits maximum. Use in scientific notation, when appropriate.
1 Always eight digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.

For money or smallmoney.

Value Output
0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

Implicit Data Type Conversion

If two expressions are combined and they are not the same data type, then the result will be implicitly converted according to these rules of precedence.

Here is a chart about implicit data type conversions in SQL Server from the MSDN library:

Implicit data type conversions in SQL Server

Miscellany

When providing Unicode values in SQL Server, prefix it with a capital 'N'. EG:

INSERT INTO tblScrap (Name) VALUES (N'This text is in Unicode. Σ.')

A User-Defined Data Type can be created with the sp_addtype systems stored procedure. Here is the syntax and some notes:

sp_addtype [@typename = ] type, 
    [@phystype = ] SystemDataType 
    [, [ @nulltype = ] '{NULL | NOT NULL | NONULL}' ] 
    [, [ @owner = ] 'OwnerName' ]

Page Modified: (Hand noted: 2007-10-12 21:34:13Z) (Auto noted: 2007-11-17 06:44:26Z)