Differences between CAST & CONVERT // CAST Vs CONVERT

Character	CAST	CONVERT
1.       Type	CAST is ANSI.	CONVERT is SQL Server specific
2.       Date values	Cast don’t has different types date formats.	You can apply Different styles for date values. Like 108, 98, 101… etc styles.
3.       Syntax	Syntax for CAST: CAST ( expression AS data_type [ ( length ) ] )	Syntax for CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
4.       For Decimal Results	Use CAST
5.       For Truncate purpose	CAST to truncate
6.       For Quick Search	CAST for quicker search

Similarities:

Variants	CAST and CONVERT variants -- always
Concatenate	CAST and CONVERT to concatenate

• Cast

1.      Cast is  ANSII Standard

2.      Cast cannot be used for Formatting Purposes.

3.      Cast cannot convert a datetime to specific format

• Convert

1.      Convert is Specific to SQL SERVER

2.      Convert can be used for Formatting Purposes.For example Select convert (varchar, datetime, 101)

3.      Convert can be used to convert a datetime to specific format

Useful Link:

http://www.techrepublic.com/blog/10-things/10-reasons-to-explicitly-convert-sql-server-data-types/324/