SQL 2005+

SQL Server Cast and Convert Functions (2)

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.

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