BlackWasp
SQL Server
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:

cast(source-value AS destination-type)

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:

Implicit conversion from data type datetime to numeric is not allowed.

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:

convert(destination-type, source-value)

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.

Convert(destination-type, source-value, style)

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

Date Styles

There are a number of date style codes that can be used as the third parameter of the Convert function. They are as follows:

Style CodeStyleFormatExample
0 or 100Default. Equivalent to not specifying a style code.mon dd yyyy hh:mmAMSep 8 2007 9:00PM
1USA date.mm/dd/yy09/08/07
2ANSI date.yy.mm.dd07.09.08
3UK / French date.dd/mm/yy08/09/07
4German date.dd.mm.yy08.09.07
5Italian date.dd-mm-yy08-09-07
6Abbreviated month.dd mmm yy08 Sep 07
7Abbreviated month.mmm dd, yySep 08, 07
8 or 10824 hour time.HH:mm:ss21:00:00
9 or 109Default formatting with seconds and milliseconds appended.mon dd yyyy hh:mm:ss:fffAMSep 8 2007 9:00:00:000PM
10USA date with hyphen separators.mm-dd-yy09-08-07
11Japanese date.yy/mm/dd07/09/08
12ISO date.yymmdd070908
13 or 113European default with seconds and milliseconds.dd mon yyyy HH:mm:ss:fff08 Sep 2007 21:00:00:000
14 or 11424 hour time with milliseconds.HH:mm:ss:fff21:00:00:000
20 or 120ODBC canonical date and time.yyyy-mm-dd HH:mm:ss2007-09-08 21:00:00
21 or 121ODBC canonical date and time with milliseconds.yyyy-mm-dd HH:mm:ss.fff2007-09-08 21:00:00.000
101USA date with century.mm/dd/yyyy09/08/2007
102ANSI date with century.yyyy.mm.dd2007/09/08
103UK / French date with century.dd/mm/yyyy08/09/2007
104German date with century.dd.mm.yyyy08.09.2007
105Italian date with century.dd-mm-yyyy08-09-2007
106Abbreviated month with century.dd mmm yyyy08 Sep 2007
107Abbreviated month with century.mmm dd, yyyySep 08, 2007
110USA date with hyphen separators and century.mm-dd-yyyy09-08-2007
111Japanese date with century.yyyy/mm/dd2007/09/08
112ISO date with century.yymmdd20070908
126ISO8601, for use in XML.yyy-mm-ddThh:mm:ss2007-09-08T21:00:00

Float / Real Styles

When converting a floating-point value held in a Float or Real data type, three formats are available. The default format (0) returns a string containing a maximum of six significant digits. If required, scientific notation is applied. The other two formats always return a string containing scientific notation and either eight or sixteen digits for the mantissa:

DECLARE @From FLOAT

SET @From = 1.23456789

PRINT convert(VARCHAR, @From, 0)    -- 1.23457
PRINT convert(VARCHAR, @From, 1)    -- 1.2345679e+000
PRINT convert(VARCHAR, @From, 2)    -- 1.234567890000000e+000

Money Styles

Three styles are available when converting Money and SmallMoney data to text. These control whether commas are used to separate digit groups and how many decimal places should be included.

Style CodeStyleExample
0Default. No commas and two decimal places.1234567.89
1Commas every three digits of the integer part and two decimal places.1,234,567.89
2No commas and four decimal places.1,234,567.8901
Link to this Page11 October 2009
TwitterTwitter RSS Feed RSS