SQL- CAST and CONVERT

Q What is the difference between CAST and CONVERT in SQL Server ?

      CAST: It is used to convert from one data type to another.CAST is ANSI
standard which means that it can also be used in other database applications.

Syntax:  
     CAST ( expression AS data_type [ ( length ) ] )

Cast_Illustration

CONVERT:Like CAST, CONVERT is also used to convert from one data type to another.It is specific to SQL Server.One major advantage of CONVERT is that it has style parameter which can be used for formatting date.

       The syntax of the CONVERT function are in a different order from the CAST function but are almost the same. It does not have AS keyword

Syntax:  

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

Convert_Illustration

       Now you will wonder when should we use CAST and when should we use CONVERT. It is very simple. Use CAST when you need to convert from one data type to another as it is a ANSI Standard. Use CONVERT when you want to take advantage of style functionality.

NOTE: Formatting is not possible on DATE datatype or DATETIME data type.First you need to convert to varchar or nvarchar and then apply style. If you use style format in DATE datatype, CONVERT function will ignore style parameter. You can see this in below snapshot. Second query is returning same result as first one because style parameter is ignored.