Sunday, 15 January 2012

Difference between 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  

Example :
declare @myDate datetime
set @myDate='20121209 12:22:45'
select convert(varchar(10),@myDate,108)
declare @mny money
set @mny =123456
select convert(varchar(10),@mny ,1)
Result : 

----------
12:22:45
----------
123,456.00

Note : 

Also you should be very careful in using the both when you convert values.
Consider the following example
declare @i int
set @i=123456
select convert(char(5),@i),cast(@i as char(5))
The result is
1.----- -----
2.*     *   
It is because the size of the variable is not enough to hold the number In this case you will not get any error.
You should in advance specify the enough length

No comments:

Post a Comment