O'Reilly logo

Microsoft SQL Server 2012 Bible by Aaron Nelson, Jose Chinchilla, Patrick LeBlanc, Jorge Segarra, Adam Jorgensen

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required