Both these functions are used to convert values from one datatype to another
But there are some differences between them
1 CAST is ANSI standard and CONVERT is specific to SQL Server
2 CAST can't be used for formating purposes.
But CONVERT can be used for formating purposes particularly on datetime and money datatype
declare @d datetime
set @d='20001210 12:34:22'
declare @m money
3 CAST can't simulate set dateformat option. But CONVERT can simulate it
The following example specifies that date value is in the format of dmy (style 103 is for dmy)
select convert(datetime,'19/12/2008 12:34:22',103)
Also you should be very careful in using the both when you convert integer values to character datatype
Consider the following example
declare @i int
select convert(char(5),@i),cast(@i as char(5))
The result is
It is becuase the size of the variable is not enough to hold the number In this case you wont get any error.
You should in advance specify the enough length