SQL Difference between CAST / CONVERT

SQL Difference between CAST / CONVERT

CAST and CONVERT usually do the same job, but CAST is a part of the SQL-92 specification and CONVERT is not. If you want your SQL to be portable between databases, use CAST. The only difference between the two is that CAST does not accept the date conversion parameter that COVERT can use to format dates as strings. [Transact-SQL user-defined functions By Andrew Novick]

The CAST() and CONVERT() functions are used to explicitly convert the information in one data type to another specified data type. there is just one small difference between these tow functions: Convert() allows you to specify the format of the result, whereas Cast() does not.

Syntax is

Cast (expression AS data_type)
Convert (data_type[(length)], expression [, style])

In this case, expression is any value or expression that you want to convert, and data_type is the new data type.

CAST / CONVERT Example

Each example retrieves the name of the product for those products that have a 3 in the first digit of their list price and converts their ListPrice to int.

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO