Datatype conversion is a common scenario that we face while writing SQL queries.
Cast and Convert are two functions provided by SQL for this purpose. But what is the difference between the two?

CAST and CONVERT provide similar functonality. CAST is according to ANSI standards and has a simpler syntax. CONVERT on the other hand provides greater functionality and flexibility for date and time conversions.

CAST

Syntax:

CAST ( expression AS data_type )

CONVERT

Syntax:

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

Note the difference in syntax here. CONVERT has additional argument for style which is used for date and time datatypes. It is an integer value.

Example SQL queries for CAST and CONVERT functions:

select CAST(getdate() as varchar)

Result:
Jun 4 2015 4:38PM

select CONVERT(varchar,getdate())

Result:
Jun 4 2015 4:41PM

select CONVERT(varchar,getdate(),3)

Result:
04/06/15

select CONVERT(varchar,getdate(),2)

Result:
15.06.04

select CONVERT(varchar,getdate(),4)

Result:
04.06.15

So which one should we use?
The performance of both CAST and CONVERT is same. As CAST is an ANSI standard, it is preferable to use it rather than convert. As depicted above, Convert provides additional flexibility for date and time formats. So, convert should be used for these scenarios where specific style needs to be applied for the display of date/time values – for rest of the scenarios, CAST should be used.