SQL 2005+

SQL Server Cast and Convert Functions

by Richard Carr, published at http://www.blackwasp.co.uk/SqlCastConvert.aspx

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

Using Styles

The Convert function provides additional functionality over Cast when used with a third argument. This parameter specifies a style to use when either the source value or the destination type is character-based. When converting some numeric types or date and time information to character data, this allows you to control the formatting of the generated string.

Try executing the following two statements against the JoBS database and comparing the results. The first statement performs no conversion of data and simply displays the information in its default format. The second statement uses the Convert function for two differing purposes. The first conversion formats the renewal dates of contracts in a VarChar column. The second converts the contract value to a VarChar so that it may be used with the concatenation operator.

SELECT
    ContractNumber,
    RenewalDate,
    ContractValue
FROM
    Contracts

SELECT
    ContractNumber,
    convert(VARCHAR(11), RenewalDate, 106) AS RenewalDate,
    '£' + convert(VARCHAR(10), ContractValue) AS ContractValue
FROM
    Contracts

When converting a character type to a date, the style can be used to remove ambiguity. For example, when the source string is '11/10/2009', the format will determine whether this is converted to 11 October or 10 November. You can see this in the following example:

DECLARE @From VARCHAR(10)

SET @From = '11/10/2009'

PRINT convert(DATETIME, @From, 103) -- Oct 11 2009 12:00AM
PRINT convert(DATETIME, @From, 101) -- Nov 10 2009 12:00AM