SQL Server - Difference between TSQL Functions CAST() and CONVERT()

SQL Server - Difference between TSQL Functions CAST() and CONVERT()

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 

 

1.declare @d datetime

2.set @d='20001210 12:34:22'

3.select convert(varchar(10),@d,108)

4.declare @m money

5.set @m=23498

6.select convert(varchar(10),@m,1)

Results are

 

1.----------

2.12:34:22

3.  

4.----------

5.23,498.00

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)

 

?

1.select convert(datetime,'19/12/2008 12:34:22',103)

Result is

 

 

1.-----------------------

2.2008-12-19 12:34:22.000

Also you should be very careful in using the both when you convert integer values to character datatype
Consider the following example

 

 

1.declare @i int

2.set @i=123456

3.select convert(char(5),@i),cast(@i as char(5))

The result is

 

 

1.----- -----

2.*     *

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