Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to achieve T-SQL data type conversion in SQL Server", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to achieve T-SQL data type conversion in SQL Server" this article.

The commonly used conversion functions are cast and convert, which are used to convert the value of the expression to another data type, and if the conversion fails, the function throws an error, causing the entire transaction to be rolled back. In the SQL Server 2012 version, two new fault-tolerant conversion functions, try_cast and try_convert, are added. If the conversion operation fails, the function returns null, which does not cause the entire transaction to fail and the transaction continues to execute.

Note: for illegal conversions explicitly defined by SQL Server, try_cast and try_convert will fail with an error message: Explicit conversion from data type int to date is not allowed.

Select try_cast (1 as date)

The conversion functions are parse and try_parse, which are only used to convert character types to date/time and numeric types, resulting in some performance consumption when parsing characters.

First, time type conversion

When converting date / time types to strings, the commonly used conversion functions are Convert and Cast,convert functions that can explicitly control the display format of date / time to some extent, while cast cannot explicitly control the display format of date / time types. I recommend using another more powerful function: FORMAT, which is used to convert date-time types into strings according to the specified format. You can also output values in a specific format.

1, commonly used conversion functions

Convert is often used to convert date / time types such as date,datetime. By specifying style parameters, you can control the format of data display.

CAST (expression AS data_type [(length)]) CONVERT (data_type [(length)], expression [, style])

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

101 mm/dd/yyyy 110 mm-dd-yyyy 111 yyyy/mm/dd 112 yyyymmdd 120 yyyy-mm-dd hh:mm:ss 121 yyyy-mm-dd hh:mm:sssssss

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

2FORMAT function, which controls the display format of date and time types

The FORMAT function is mainly used to format and display the date/time type and numeric type. The parameter format is used to specify the format of the display, giving the user more control over the format. The culture parameter is optional and is used to specify the displayed language. The function returns the data type of the value NVARCHAR. If the format conversion fails, the function returns NULL:

FORMAT (value, format [, culture])

When converting date/time, specify the format of date / time display in the format parameter. In general, the date / time format uses the following key characters as placeholders: yyyy, MM, and dd are used to indicate year, month and day, while hh, mm, and ss are used to represent hours, minutes, seconds, and use "/", "-" and so on as connectors, for example:

DECLARE @ d DATETIME = GETDATE (); SELECT FORMAT (@ d, 'dd/MM/yyyy',' en-US') AS 'DateTime Result'

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

FORMAT (123456789) AS 'Custom Number Result

Second, fault-tolerant conversion function

TRY_CAST and TRY_CONVERT are fault-tolerant conversion functions that attempt to convert the value of an expression to a specified type, and return a value of the specified type if the conversion is successful, or NULL if the conversion attempt fails If a request is made to convert one type to another explicitly prohibited data type, the attempt to convert fails and an error message is thrown, that is, the attempted conversion can be fault-tolerant, but an "illegal" conversion cannot be done.

TRY_CAST (expression AS data_type [(length)]) TRY_CONVERT (data_type [(length)], expression [, style])

1Legend trycast returns null

SELECT CASE WHEN TRY_CAST ('test' AS float) IS NULL THEN' Cast failed' ELSE 'Cast succeeded' END AS Result

2 failed to convert the dint trycast. Return error

SELECT TRY_CAST (4 AS xml) AS Result

The error message is Explicit conversion from data type int to xml is not allowed.

3Grammer trycast converted successfully

SET DATEFORMAT mdy;SELECT TRY_CAST ('12Comp31and2010' AS datetime2) AS Result

4 date/time conversion is often used to convert the string type to a string in a specified format

The system predefines the Style, which specifies the format of the final display date/time through the style parameter

SELECT TRY_CONVERT (varchar (8), getdate (), 112) AS Result

Third, the performance of the conversion

The performance of the conversion function is different. After testing, the conversion performance of cast and convert is the best, which is better than that of try_cast and try_convert, while the conversion performance of Cast is better than that of convert.

These are all the contents of the article "how to convert T-SQL data types in SQL Server". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.