CAST VS CONVERT

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.

[ALSO READ A FEW MORE...]

• PRIMARY KEY vs UNIQUE KEY
• @@IDENTITY vs SCOPE_IDENTITY()
• SEQUENCE vs IDENTITY
• TINYINT vs SMALLINT
• SMALLINT vs INT
• INT vs BIGINT
• DECIMAL/NUMERIC vs FLOAT
• FLOAT vs REAL
• DECIMAL/NUMERIC vs REAL
• SMALLMONEY vs MONEY
• SMALLDATETIME vs DATETIME
• DATETIME vs DATETIME2(N)
• DATETIMEOFFSET vs DATE
• DATETIMEOFFSET vs TIME
• SYSUTCDATETIME vs SYSUTCDATE
• CHAR vs NCHAR
• CHAR or VARCHAR
• VARCHAR vs NVARCHAR
• VARCHAR vs VARCHAR(MAX)
• TEXT vs VARCHAR(MAX)
• BINARY vs VARBINARY
• IMAGE vs VARBINARY(MAX)
• CAST vs CONVERT
• LEN() vs DATALENGTH()
• UNION vs UNION ALL
• DELETE vs TRUNCATE
• Temporary Table vs Temporary Variable
• LEFT JOIN vs RIGHT JOIN
• LEFT OUTER JOIN vs RIGHT OUTER JOIN
• RAISERROR vs THROW
• Function vs Stored Procedure
• QUOTED_IDENTIFIER ON vs QUOTED_IDENTIFIER OFF