Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Advice from Aunt Kathi

Kathi Kellenberger is a Sr. Consultant with Pragmatic Works. She is an author, speaker and trainer.

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)

------------------------------

01/23/2006

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

select convert(varchar,getdate(),103)

------------------------------

23/01/2006

Here is an example formating money:

declare @amount money

set @amount = 1000000

select @amount

select convert(varchar,@amount,1)

---------------------

1000000.00

------------------------------

1,000,000.00

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.

Comments

Posted by Anonymous on 24 January 2006
Kathi Kellenberger provides some pointers on converting in her Cast vs Convert post. ...
Posted by Anonymous on 24 January 2006
Kathi Kellenberger provides some pointers on converting in her Cast vs Convert post. ...
Posted by dj_e31337 on 25 May 2011

CAST is ANSI compliant and CONVERT is specific to Microsoft SQL Server

Leave a Comment

Please register or log in to leave a comment.