CONVERT() in SQL Server

Posted on by Ian

In SQL Server, the CONVERT() function converts an expression of one data type to another.

Here’s a quick overview of the function with examples.

Syntax

The syntax goes like this:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Where expression is the expression to convert, data_type is the new data type, and length is an optional length for the new data type.

The optional style argument can be used to specify how the function should translate the expression argument. For example, you could use this argument to specify the date format.

Example

Here’s an example of converting a string to decimal:

SELECT CONVERT(DECIMAL(5,2), '007');

Result:

7.00

String to Date

Here’s an example of casting a string to a date:

SELECT CONVERT(date, '09 Feb 2030');

Result:

2030-02-09

In this case the CONVERT() function was able to determine which date parts are which because I provided the date in a format that it recognises.

Passing a value that can’t be cast as a date results in an error:

SELECT CONVERT(date, 'My Birthday');

Result:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Locale

The language of the current session can make a difference in how dates are interpreted.

Here’s what happens when using the British language:

SET LANGUAGE British;
SELECT CONVERT(date, '09/02/2030');

Result:

2030-02-09

In this case, the date was interpreted as being the ninth day of February.

Let’s change the language to us_English:

SET LANGUAGE us_English;
SELECT CONVERT(date, '09/02/2030');

Result:

2030-09-02

This time it interpreted the date as being the second day of September.

The style Argument

We can use the optional style argument to specify how the expression should be translated.

Example:

SET LANGUAGE British;
SELECT 
    CONVERT(date, '09/02/2030') AS "British",
    CONVERT(date, '09/02/2030', 101) AS "US",
    CONVERT(date, '09/02/30', 1) AS "US (short)",
    CONVERT(date, '20300902', 112) AS "ISO",
    CONVERT(date, '09.02.2030', 104) AS "German";

Result:

+------------+------------+--------------+------------+------------+
| British    | US         | US (short)   | ISO        | German     |
|------------+------------+--------------+------------+------------|
| 2030-02-09 | 2030-09-02 | 2030-09-02   | 2030-09-02 | 2030-02-09 |
+------------+------------+--------------+------------+------------+

Valid styles:

String Concatenation

Here’s an example of casting a numeric value to a string in order to concatenate the value with a string:

SELECT 'Age: ' + CONVERT(varchar(10), 27);

Result:

Age: 27

Here’s what happens if we don’t convert it first:

SELECT 'Age: ' + 27;

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Age: ' to data type int.

When attempting to concatenate two values with different data types, SQL Server needs to implicitly convert one of the values to use the data type of the other, so that they’re both the same data type before it can perform the concatenation. It follows the rules of data type precedence to do this conversion.

In SQL Server, the int data type has a higher precedence than varchar.

Therefore, in the above example, SQL Server attempts to convert the string to an integer, but this fails because the string cannot be converted to an integer.

Truncating Text

One handy little trick we can perform with the CONVERT() function is to truncate longer strings to a more readable length.

Here’s an example of truncating text selected from a database table:

SELECT
    ProductName,
    CONVERT(varchar(20), ProductName) AS Truncated
FROM Products;

Result:

+---------------------------------+----------------------+
| ProductName                     | Truncated            |
|---------------------------------+----------------------|
| Left handed screwdriver         | Left handed screwdri |
| Long Weight (blue)              | Long Weight (blue)   |
| Long Weight (green)             | Long Weight (green)  |
| Sledge Hammer                   | Sledge Hammer        |
| Chainsaw                        | Chainsaw             |
| Straw Dog Box                   | Straw Dog Box        |
| Bottomless Coffee Mugs (4 Pack) | Bottomless Coffee Mu |
| Right handed screwdriver        | Right handed screwdr |
+---------------------------------+----------------------+

More Information

The CONVERT() function works in a similar way to the CAST() function, and many data conversions can be done using either one. That said, there are differences between them.

See Microsoft’s documentation for CAST() and CONVERT() for more detailed information about using this function.