Data Type CAST and CONVERT (Transact-SQL)
It is used to convert an expression of one data type to another.
Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function.
Explicit conversions are those conversions that require the CAST or CONVERT function to be specified.
CAST
|
CONVERT
|
It is ANSII Standard.
|
It is Specific to SQL SERVER.
|
It can’t be used for Formatting Purposes.
For example PRINT CAST('12/01/2016' AS DATE)
OUTPUT:
2016-12-01
Here, By default it produces result ISO8601 standard.
|
It can be used for Formatting Purposes by passing the appropriate style as parameter.
For example
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.
|
The following chart shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.
