CAST and CONVERT Conversion Functions

Conversion functions in T-SQL allow us to transform a value from one data type to another.

In the database world, data types are sacred. A number is a number and text is text. SQL Server is a strongly typed language, meaning it doesn’t like it at all when we try to mix apples and oranges.

If you try to run this:

SELECT 'El precio es: ' + 50;

SQL Server will throw an error. Why? Because it’s trying to add (mathematically) text with a number, and that makes no logical sense to the engine.

To solve this, we need to perform a type conversion. T-SQL offers us two main tools for this: CAST and CONVERT.

CAST: The ANSI Standard

The CAST function is the standard (ANSI SQL) way to convert data from one type to another.

CAST(Value AS NewType)

Let’s go back to the previous error example. For it to work, we need to convert the number 50 to text (VARCHAR) before concatenating it.

SELECT 'El precio es: ' + CAST(50 AS VARCHAR(10));
-- Result: "El precio es: 50"

We can also use it to truncate text or reduce the precision of decimals:

SELECT CAST('Texto Demasiado Largo' AS VARCHAR(5));
-- Result: "Texto"

SELECT CAST(123.4567 AS DECIMAL(10,2));
-- Result: 123.46 (Rounded)

CONVERT: The Power of SQL Server

The CONVERT function is specific to SQL Server. It does the same as CAST (change types), but allows adding the Style.

When we convert dates to text, CAST gives us a default format that we often don’t like. CONVERT allows us to specify how we want that date to look.

CONVERT(NewType, Value, [Style])

The “Style” Parameter (Dates)

This is the main reason we use CONVERT. The third parameter is a numeric code that defines the output format.

Here are the most useful codes you will use day in and day out (especially 103 for European format and 112 for ISO):

DECLARE @Today DATETIME = GETDATE();

SELECT
    CAST(@Today AS VARCHAR(20)) AS WithCast,
    CONVERT(VARCHAR(20), @Today, 103) AS SpanishFormat,
    CONVERT(VARCHAR(20), @Today, 112) AS ISOFormat;

SELECT 10 * '5';
-- Result: 50

-- This would give an ERROR and stop execution
-- SELECT CAST('Hola' AS INT);

-- This returns NULL and continues
SELECT TRY_CAST('Hola' AS INT);

SELECT OriginalValue
FROM DirtyDataTable
WHERE TRY_CAST(OriginalValue AS INT) IS NOT NULL;
-- Only returns rows that are actually numbers

SELECT PARSE('Monday, 13 December 2010' AS datetime2 USING 'en-US');
SELECT PARSE('100,00 €' AS money USING 'es-ES');