Monday 9 July 2018

CONVERT VS TRY_CONVERT


What are the differences between CONVERT and TRY_CONVERT  [CONVERT VS TRY_CONVERT] ?

CONVERT
TRY_CONVERT
1
CONVERT is an existing feature.


Returns the result of an expression, translated to the requested data type in SQL Server.
TRY_CONVERT is a new feature in SQL Server 2012.

Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null or ‘*’ or error in SQL Server.


2
SYNTAX:

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

data_type [ ( length ) ]
The target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used.

expression
Any valid expression, value must be a valid representation of the requested data type, or returns Error

length
An optional integer that specifies the length of the target data type. The default value is 30.

style
An integer expression that specifies how the CONVERT function will translate expression. For a style value of NULL, NULL is returned. data_type determines the range.

SYNTAX:

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

data_type [ ( length ) ]
 The data type into which to cast expression.



expression
 The value to be cast.  value must be a valid representation of the requested data type, or TRY_ CONVERT returns NULL or ‘ERROR’ or ‘*’

length
An optional integer that specifies the length of the target data type. The default value is 30.


style
Optional integer expression that specifies how the TRY_CONVERT function is to translate expression, style accepts the same values as the style parameter of the CONVERT function. For more information, see CAST and CONVERT (Transact-SQL).



The range of acceptable values is determined by the value of data_type. If style is null, then TRY_CONVERT returns null.

3
Return Types
Returns expression, translated to data_type.
Return Types
Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null or error or ‘**’
4
EXAMPLES

PRINT CONVERT(VARCHAR(20), GETDATE(), 101)

OUTPUT: 
12/01/2016

Here, We are able to pass style 101 for producing the result as US standard.

EXAMPLES

SELECT TRY_CONVERT(INT, '1') AS Result;  --> 1
OUTPUT:
1


-- TRY_CONVERT
SELECT TRY_CONVERT(INT , NULL)  AS Result     --> NULL
OUTPUT:
NULL

SELECT TRY_CONVERT(INT , 99)  AS Result       --> 99
OUTPUT:
99

SELECT TRY_CONVERT(INT , 99, 1-10) AS Result  --> 99
OUTPUT:
99

SELECT TRY_CONVERT(INT , 'ABC') AS Result   --> NULL
OUTPUT:
NULL

SELECT TRY_CONVERT(SMALLINT, 'ABC') AS Result  --> NULL
OUTPUT:
NULL

SELECT TRY_CONVERT(TINYINT, 255) AS Result   --> 255
OUTPUT:
255

SELECT TRY_CONVERT(TINYINT, 256) AS Result   --> NULL
OUTPUT:
NULL

SELECT TRY_CONVERT(VARCHAR(1), 99) AS Result  --> '*'  It is not an error but the output is invalid
OUTPUT:
'*'  It is not an error but the output is invalid

SELECT TRY_CONVERT(VARCHAR(2), 99)  --> 99
OUTPUT:
99

SELECT TRY_CONVERT(xml, 4) AS Result;  --> 'error' 
OUTPUT:
Explicit conversion from data type int to xml is not allowed.

SELECT TRY_CONVERT(xml, 'Root') AS Result; --> Root 
OUTPUT:
Root

SELECT TRY_CONVERT(xml, '<root><Parent>Govind</parent></root>') AS Result; --> NULL 
OUTPUT:
NULL

SELECT TRY_CONVERT(xml, '<root><parent>Govind</parent></root>') AS Result; --> <root><parent>Govind</parent></root> 
OUTPUT:
<root><parent>Govind</parent></root>





1 comment:

  1. What is a good table game? - JDH Hub
    포천 출장마사지 games › table-game 영주 출장샵 › games › table-game The basic concept behind the casino's slots and table games is 광주 출장마사지 to hold, press, and hold the two hands, in one position to 평택 출장마사지 push the ball away and spin 안양 출장마사지 the reels.

    ReplyDelete