Microsoft® SQL Server® 2012 Bible

207

Chapter 8: Data Types, Expressions, and Scalar Functions

8

Soundex value for both parameters, which prevents it from taking advantage of pre-

stored Soundex values.

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

ferent data type with optional formatting. The fi rst 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 three-digit counterpart provides a four-digit year.

For example, style 1 provides 01/01/03, whereas style 101 provides 01/01/2003. The styles

marked with an asterisk (*) in Table 8-7 are the exceptions to this rule.

c08.indd 207c08.indd 207 7/30/2012 4:21:17 PM7/30/2012 4:21:17 PM

http://www.it-ebooks.info