Explain the differences between CAST and CONVERT ?

Answer:

Both are used for the same purpose. They both are used to convert from one data type to another specified data type.
The major differences are:
a) CAST cannot allows you to specify the format of the result which you wants to convert, whereas CONVERT allows it.
b) CAST is a part of SQL-92 specification whereas CONVERT is not the part.
c) CONVERT can be used to format dates as strings, whereas CAST cannot.

Example:

Usage of CAST:

USE Sample

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(ListPrice AS int) LIKE '3%';

GO

Usage of CONVERT:

USE Sample

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(int, ListPrice) LIKE '3%';

GO