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>
|
What is a good table game? - JDH Hub
ReplyDelete› 포천 출장마사지 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.