SQL Server T-SQL data type conversion details, servert-SQL

SQL Server T-SQL data type conversion details, servert-SQL

Cast and convert are commonly used to convert the type of the value obtained by the expression to another data type. If the conversion fails, this function throws an error, causing the entire transaction to roll back. In SQL Server 2012, two fault-tolerant conversion functions are added: try_cast and try_convert. If the conversion operation fails, the function returns null, which does not cause the entire transaction to fail and the transaction continues to run.

Note: For Explicit conversions of SQL Server definitions that are not legal, try_cast and try_convert will fail and an error message is thrown: 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. They are only used to convert character types to date/time and numeric types, resulting in certain performance consumption during Character Parsing.

1. Time type conversion

When converting the date/time type to a string, the commonly used conversion functions are Convert and Cast. The convert function can explicitly control the display format of the date/time to a certain extent, however, cast cannot explicitly control the display FORMAT of the date/time type. I recommend another more powerful function: FORMAT, which is used to set the date and time type, converts a value to a string in the specified format or outputs the value in the specified format.

1. Common conversion functions

Convert is often used to convert date, datetime, and other date/time types. by specifying the style parameter, you can control the data display format.

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

Common styles and their display formats are as follows:

101mm/dd/yyyy 110mm-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. It can only be displayed in the format defined by the system. It is not flexible enough. SQL Server provides a more flexible Conversion Function FORMAT.

2. The FORMAT function controls the display FORMAT of the date and time types.

The FORMAT function is mainly used to format and display the date/time type and value type. The FORMAT parameter is used to specify the display format, giving users more freedom to control the FORMAT. The culture parameter is optional, specifies the Display language. The data type returned by this function is NVARCHAR. If the format conversion fails, this function returns NULL:

FORMAT ( value, format [, culture ] ) 

When converting date/time, specify the date/time display format in the format parameter. Generally, the date/time format uses the following key characters as placeholders: yyyy, MM, and dd are used to represent the year, month, and day, while hh, mm, and ss are used to represent the hour, minute, and second, and "/" is used. "-" is used as a connector, for example:

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

When the numeric type is converted, # is used in the format parameter to represent a number, and the corresponding connector is used to splice it into a Number format character, for example:

FORMAT(123456789,'###-##-####') AS 'Custom Number Result

Ii. Fault-Tolerant conversion functions

TRY_CAST and TRY_CONVERT are error-tolerant conversion functions. This function tries to convert the expression value to the specified type. If the conversion is successful, a value of the specified type is returned. If the conversion fails, NULL is returned; if a request converts a type to another explicitly prohibited data type, the attempt to convert the Data fails and an error message is thrown. That is to say, the attempt to convert the data type has certain error tolerance. However, cannot perform "illegal" conversion operations.

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

1, try_cast returns null

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

2. If try_cast fails to be converted, error is returned.

SELECT TRY_CAST(4 AS xml) AS Result;

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

3. try_cast is successfully converted.

SET DATEFORMAT mdy;SELECT TRY_CAST('12/31/2010' AS datetime2) AS Result;

4. try_convert is used to convert the date/time type to a string in the specified format.

The system predefines the Style. The format of the date/time is specified by the style parameter.

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

Iii. Conversion Performance

The conversion function performance is different. After testing, cast and convert have the best conversion performance, which is better than try_cast and try_convert. Cast has a better conversion performance than convert.

Reference:

Performance Comparison of the SQL Server PARSE, CAST, CONVERT and TRY_PARSE, TRY_CAST, TRY_CONVERT Functions

CAST and CONVERT (Transact-SQL)