SQL 2005+

SQL Server Cast and Convert Functions

The thirty-eighth part of the SQL Server Programming Fundamentals tutorial discusses the cast and convert functions provided by Transact-SQL (T-SQL). These two standard functions allow values to be explicitly converted from one data type to another.

Data Type Conversion

When working with data from SQL Server tables, it is common to wish to convert values from one data type to another. In most situations this can be achieved implicitly, by simply using a value as if it were already the desired type. For example, assigning an integer value to a floating-point variable. In these cases, no additional functions need be used.

Some combinations of source and destination data types do not permit implicit conversion. For example, although a DateTime value can be represented using a floating-point number, you may not implicitly convert from DateTime to a Float. You can, however, perform the conversion explicitly using the Cast and Convert functions.

Cast and Convert provide similar functionality. Each allows you to explicitly convert a value, or column of values in a query, from one data type to another. The key difference between the two functions is that Cast is compliant with ANSI standards, making it portable between database management systems (DBMS), whereas Convert is specific to T-SQL. As Convert does not need to provide an ANSI-specific syntax, it is more powerful but less likely to work with another DBMS.

Using Cast and Convert

Most of the example code in this article can be executed directly using SQL Server Management Studio or any other tool that permits execution of T-SQL statements. Where the samples use information from database tables, the JoBS database is required. This is the tutorial database that has been created during the course of these articles. If you do not have an up-to-date copy of the database, you can create one using the script that can be downloaded via the link at the top of the page.

Casting Values

The syntax for the Cast function is very simple. Firstly you must specify the value that you wish to convert and secondly the desired data type, optionally including size information. The two items are separated with the "AS" keyword and used within the Case function's parentheses, as follows:

To demonstrate, try executing the following script, which casts a DateTime value to a fixed-point number.

DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)

SET @From = '2009-10-11T11:00:00'
SET @To = cast(@From AS NUMERIC(10,5))

PRINT @To   -- 40095.45833

The above sample shows a situation where explicit conversion is required and where implicit conversion would be rejected. If you were to remove the Cast, the assignment of the @To variable would result in an error:

When casting values between data types you should always consider the precision of the destination type. If the precision is lower than that of the original value, you may lose information during the conversion, due to rounding or truncation of the original value. Further use of the result can compound the problem. For example, consider the following script. Here the DateTime is converted to a fixed-point number. The value is then cast to a DateTime that is not the same as the original value, due to a rounding error.

DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)
DECLARE @Final DATETIME

SET @From = '2009-10-11T11:00:00'
SET @To = cast(@From AS NUMERIC(10,5))
SET @Final = @To 

PRINT @From     -- Oct 11 2009 11:00AM
PRINT @Final    -- Oct 11 2009 10:59AM

Using the Convert Function

The basic form for Convert performs the same functionality as Cast, using a different syntax:

We can therefore rewrite the first Cast example from above as follows:

DECLARE @From DATETIME
DECLARE @To NUMERIC(10,5)

SET @From = '2009-10-11T11:00:00'
SET @To = convert(NUMERIC(10,5), @From)

PRINT @To       -- 40095.45833