Microsoft SQL Server 2012 Bible

Data-Type Conversion Functions

Converting data from one data type to another data type is often handled automatically by SQL Server. Many of those conversions are implicit, or automatic.

Explicit conversions require a CAST() or CONVERT() function:

• CAST(Input as data type): The ANSI standard SQL means to convert from one data type to another. Even when the conversion can be performed implicitly by SQL Server, using the CAST() function forces the wanted data type.
• CAST() is actually programmed slightly differently than a standard function. Rather than separate the two parameters with a comma (as most functions do), the data passed to the CAST() function is followed by the as keyword and the requested output data type:

SELECT CAST('Away' AS NVARCHAR(5)) AS ‘Tom Hanks'

Result:

TOM HANKS
---------
AWAY

Another example:

 SELECT CAST(123 AS NVARCHAR(15)) AS Int2String

Result:

INT2STRING   
---------------
123

• CONVERT(datatype, expression, style): Returns a value converted to a different data type with optional formatting. The first parameter of this non-ANSI SQL function is the wanted data type to be applied to the expression:

CONVERT (data type, expression[, style])

The style parameter usually refers to the optional date styles listed in Table 8.7. The style is applied to the output during conversion from datetime to a character-based data type, or to the input during conversion from text to datetime. Generally, the one- or two-digit style provides a two-digit year, and its ...