Microsoft® SQL Server® 2012 Bible

209

Chapter 8: Data Types, Expressions, and Scalar Functions

8

The following code demonstrates the CONVERT() function:

SELECT CURRENT_TIMESTAMP AS RawDate,

CONVERT (NVARCHAR(25), CURRENT_TIMESTAMP, 100) AS Date100,

CONVERT (NVARCHAR(25), CURRENT_TIMESTAMP, 1) AS Date1;

Result:

RawDate Date100 Date1

--------------------------- ---------------------- ----------

2009-11-17 10:27:27.413 Nov 17 2001 10:27AM 11/17/01

An additional data-type conversion function provides a fast way to move data between text

and numeric. STR(number, length, decimal): returns a string from a number:

SELECT STR(123,6,2) AS [Str];

Result:

Str

-----

123.00

SQL Server 2012 introduces three new built-in conversion functions. Two of the three are com-

pletely new to SQL Server, whereas the third is a slight modifi cation of an existing function.

The Parse(string_value as data_type using culture) and Try_

Parse(string_value as data_type using culture) are similar for what values

they expect and what is returned. The following scripts are samples of how to use both

functions:

SELECT PARSE('123' AS INT) AS Parsed

Result:

Parsed

---------

123

SELECT TRY_PARSE('123' AS DATETIME) TryParse

Result:

TryParse

----------

NULL

The string_value in both functions represents the formatted value to be parsed into the

specifi ed data type. The values can be parsed only into Numeric and Date/Time data types.

The culture, which is optional, represents various SQL Server languages. The following

script illustrates how to execute the Try_Parse function using the culture function:

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

http://www.it-ebooks.info