Printed 2017/07/21 05:11PM

Cast vs Convert


Sometimes you must "convert" a column or variable from one data type to another.  For example, if you want to compare a numeric value to a string, you must often cast one to the other so that the comparison can be made.   This can be done with either CAST or CONVERT.

I've noticed from talking with colleages that CAST is usually preferred. I think it has something to do with using cast in C++ or C#: it just sounds more professional or maybe cooler to say CAST.  The CONVERT function may actually be more helpful in many cases. 

The CONVERT function has a third optional parameter called STYLE that allows you to format the value when converting a date, money, float or real value to a varchar.  SQL Server 2005 has a new set of styles for converting to XML data.  I haven't had a chance to try the XML styles yet, but I'll show you some examples that I have used at work.

Here is an example that removes the time:

select convert(varchar,getdate(),101)



There are many formats or styles available.  Here is a non-American example:

select convert(varchar,getdate(),103)



Here is an example formating money:

declare @amount money

set @amount = 1000000

select @amount

select convert(varchar,@amount,1)





I have found the convert function very useful, especially when I want to work with the date and not the time.  I haven't found anything stating that either CONVERT or CAST is better to use if you are not concerned about formatting.

Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.