Conversion functions in SQL

 These all are the function in SQL which is used to converts an expression from one data type to another data type and these all function are known as Conversion "Functions in SQL".

 

Conversion functions in SQL

CAST function in SQL : - 

CAST in SQL helps to converts an expression from one data type to another data type. If the conversion fails, CAST function would come back with a mistake. Otherwise, it'll give the conversion value.

Syntax  : - 

CAST( expression AS type [ (length) ] )

length: - length of the resulting data type like char, int, float, etc.

Example : - 

SELECT CAST(13.35 AS int);
Result: 13          (result is truncated)

SELECT CAST(14.56 AS float);
Result: 14.56       (result is not truncated)

SELECT CAST(14.7 AS varchar);
Result: '14.7'

SELECT CAST(149.6 AS varchar(4));
Result: '15.9'

SELECT CAST('15.6' AS float);
Result: 15.6

SELECT CAST('2014-05-02' AS datetime);
Result: '2014-05-02 00:00:00.000'

CONVERT function in SQL : -

CONVERT method in SQL does the same as CAST function "converts an expression from one data type to another data type."
But the syntax is different in this method tell the type of data type in which you wish to convert the expression to and type used to tell about the format used to convert between data types.

Syntax  : - 

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

Example : -

SELECT CONVERT(int, 13.85);
Result: 13          (result is truncated)

SELECT CONVERT(float, 13.85);
Result: 13.85       (result is not truncated)

SELECT CONVERT(varchar, 15.7);
Result: '15.7'

SELECT CONVERT(varchar(4), 15.7);
Result: '15.7'

SELECT CONVERT(float, '15.6');
Result: 15.6

SELECT CONVERT(datetime, '2014-05-02');
Result: '2014-05-02 00:00:00.000'

SELECT CONVERT(varchar, '05/02/2014', 101);
Result: '05/02/2014'

TRY_CAST function in SQL Server : -

TRY_CAST function also does the same work as the previous two functions did but this function will return NULL for any error. Otherwise, it will return the converted value.

Syntax  : - 

TRY_CAST( expression AS type [ (length) ] )

Example : -

SELECT TRY_CAST(13.85 AS int);
Result: 13
(result is a truncated int value)

SELECT TRY_CAST(13.85 AS float);
Result: 13.85
(returned as a float value and is not truncated)

SELECT TRY_CAST('14 Main St.' AS float);
Result: NULL
(result is NULL because conversion failed since this string value can not be converted to a float)

SELECT TRY_CAST(15.6 AS varchar);
Result: '15.6'
(result is returned as varchar)

SELECT TRY_CAST(15.6 AS varchar(2)); 
Result: NULL
(result is NULL because conversion failed since the value will not fit in a 2 character varchar)

SELECT TRY_CAST('2018-09-13' AS datetime);
Result: '2018-09-13 00:00:00.000'
(result is returned as a datetime)

TRY_CONVERT function in SQL Server : -

TRY_CONVERT function could be used at the place of CONVERT function in SQL because this function also used to "converts an expression from one data type to another data type." and TRY_CONVERT function will return NULL for any error. Otherwise, it will return the converted value.

Syntax  : - 

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

Example : -

SELECT TRY_CONVERT(int, 14.80);
Result: 14


SELECT TRY_CONVERT(float, 14.75);
Result: 14.75
(result is returned as a float value and is not truncated)

SELECT TRY_CONVERT(float, '14 Main St.');
Result: NULL


SELECT TRY_CONVERT(varchar, 15.6);
Result: '15.6'
(result is returned as a varchar)

SELECT TRY_CONVERT(varchar(2), 15.6); 
Result: NULL


SELECT TRY_CONVERT(datetime, '2018-09-13');
Result: '2018-09-13 00:00:00.000'


SELECT TRY_CONVERT(varchar, '2018-09-13', 101);
Result: '09/13/2018'
(result is returned as a varchar with a style of 101 - mm/dd/yyyy (US standard) )