TSQL Data type Conversion

The usual conversion functions are cast and convert, which convert the type of the value derived from the expression to another data type, and if the conversion fails, the function throws an error that causes the entire transaction to be rolled back. In the SQL Server 2012 release, two new fault-tolerant conversion functions were added: Try_cast and Try_convert, if the conversion operation fails, the function returns NULL, does not cause the entire transaction to fail, and the transaction continues to execute.

Note: For SQL Server explicitly defined illegal conversions, try_cast and Try_convert fail with an error message:Explicit conversion from data type int to date was not Allo Wed.

Select try_cast (1 as Date)

Conversion functions are parse and try_parse, and are used only to convert character types to date/time and numeric types, which can produce a certain performance drain when parsing characters.

One, Time type conversion

When converting a date/time type to a string, the commonly used conversion function is convert and cast,and the CONVERT function can control the display format of the date/time to a certain extent, while the cast has no explicit control over the display format of the date/time type. I recommend using another more powerful function: format, which is used to convert datetime types to strings in the specified format, or to output values in a specific format.

1, commonly used conversion functions

Convert is commonly used for converting date/time types such as Date,datetime, and can control the format of data display by specifying the style parameter

CAST  as  [] )CONVERT[ ] [  ] )

The commonly used style and its display format are as follows:

• 101 mm/dd/yyyy
• Mm-dd-yyyy
• 111 YYYY/MM/DD
• Yyyymmdd
• YYYY-MM-DD Hh:mm:ss
• 121 Yyyy-mm-dd Hh:mm:sssssss

The style of the CONVERT function is a number that is difficult to remember and can only be displayed in a system-defined format, not flexible enough. SQL Server provides a more flexible conversion function, format.

2,format function to control the display format of date and time types

The Format function is primarily used for formatting display date/time types and numeric types, the parameter format is used to specify the format to display, giving the user more control over the format, the culture parameter is optional, and the data type of the function return value is nvarchar , if the format conversion fails, the function returns null:

[]

When converting Date/time, specify the date/time display format in the format parameter, typically, the date/time format uses the following key characters as placeholders: yyyy, MM, DD are used to denote: year, month, and day, while HH, MM, SS are used to denote: hours, minutes, seconds, and use " /","-"and so on as a connector, for example:

DECLARE @d DATETIME = GETDATE ();   SELECT @d ' dd/mm/yyyy ' ' en -US '  as ' DateTime Result '  

When converting a numeric type, use # in the parameter format to represent a number, and use the corresponding connector to stitch the formatted characters into numbers, for example:

FORMAT (123456789,'###-##-####'as'Custom number Result 

II, Try_cast and Try_convert

If the expression conversion succeeds, returns the value of the expression after the strong turn, returns NULL if the conversion fails, or throws an error if the conversion fails with two explicitly forbidden data types.

try_cast takes the value passed to it and tries to convert it to the specified data_type. If The cast succeeds, try_cast Returns the value as the specified data_type; If an error occurs, NULL is returned. However if you request a conversion that's explicitly not permitted and then Try_cast fails with an error.

 as [][][] )

1,try_cast returns null

SELECT  Case  when Try_cast ('test'asfloatisNULLthen                   'cast failed'            ELSE'cast succeeded '        END  as Result;

2,try_cast conversion failed, return error

SELECT Try_cast (4 as as Result;

Explicit conversion from the data type int to XML are not allowed.

3,try_cast Conversion Success

SET DateFormat mdy; SELECT Try_cast ('12/31/2010' as as Result;

Third, the performance of the conversion

The performance of the conversion function is different, and the conversion performance of cast and convert is better than that of Try_cast and Try_convert, and cast is a little better than convert.

Reference Documentation:

Performance Comparison of the SQL Server PARSE, CAST, CONVERT and Try_parse, Try_cast, Try_convert Functions

CAST and CONVERT (Transact-SQL)

TSQL Data type Conversion