SQL: CONVERT vs. CAST

SQL: CONVERT vs. CAST

By: Zack Turnbow

Introduction

When writing stored procedures or other SQL queries, too often there is a need to have data in a specific data type. The two common functions used to achieve this is the CONVERT and CAST functions.

Need help with Windows Dedicated Hosting? Try Server Intellect. I'm a happy customer!

I’ve often wondered which one should be used and which one is the most preferred. This article will take a look at the differences between the two functions and give a few pointers as to which one to use according to the circumstances.

History

First, let’s take a look at a little history about these two functions. The CONVERT function can only be used in SQL Server where as the CAST function derives from the ANSI standard.

The fact that the CAST function can be used across several databases, means that it is a bit more ‘watered down’ than CONVERT which gives much more flexibility when converting date and time and money types.

The CAST function is much better at preserving the decimal places when converting decimal and numeric data types. Most of developers and DBA’s that I have come across prefer to us the CAST function although CONVERT tends to be a bit for useful since it allows you to format the output.

Implementation

So, let’s look at some examples on how the CAST and CONVERT functions are used. To simply take a datetime stamp and get a string the queries would look something like this:

As the results for these queries show, the output is the same:
Aug 14 2008 10:35AM

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.

The real power of the CONVERT function is that you can format the output in any form you like. 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.
In the CONVERT query above, the option 100 signifies what format the output will be in. A complete list of the different options can be found here. Here are a few more examples of the CONVERT function formatting different data types.

To get the date only:

With the result of:
08/14/2008

To get Month Day, Year:

With the result of:
Aug 14, 2008

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!

To convert a money value into a string value:

With the result of:
4,000.00

Both the CONVERT and CAST functions can be used together to achieve some specific uses. One such use is when there is a need to cast a variable explicitly as a datetime value to match a specific database column. The query could look something like this:

With the results of:
2008-08-14 00:00:00.000

Whereas using just the CONVERT function like this:

Try Server Intellect for Windows Server Hosting. Quality and Quantity!

Only yields this:
2008.08.14

From the examples provided, is easy to see how useful the CONVERT function is compared to the CAST function, especially when working with dates and times.
If formatting is not an issue then, perhaps the CAST function would be better to use. The general use of using one over the other is strictly a person’s preference.

What have we learned?

• What databases use CONVERT and CAST.

• What some of the general uses are for CAST and CONVERT.

• When to use CAST.

• When to use CONVERT.

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.