The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

CAST, CONVERT, TRY_CAST and TRY_CONVERT: Control data types

SQL Server can convert some values implicitly, without the query requesting the conversion explicitly. Relying on this can hide errors, alter comparisons, or prevent efficient use of an index—a structure that organizes values to make rows easier to access.

Cast functions make the expected type explicit. Its TRY variants return NULL when a compatible value cannot in principle be converted.

The idea

CAST: express a conversion with standard syntax

CAST(Expresion AS TipoDeDato)

CONVERT: convert with SQL Server syntax and styles

CONVERT(TipoDeDato, Expresion, Estilo)

TRY_CAST: return NULL when conversion fails

TRY_CAST(Expresion AS TipoDeDato)

TRY_CONVERT: convert with a style without stopping on an invalid value

TRY_CONVERT(TipoDeDato, Expresion, Estilo)

Example: Controlling a Decimal Calculation

SELECT
    pd.IdPedido,
    pd.NumeroPartida,
    CAST(
        pd.Cantidad * pd.PrecioUnitario
        * (1 - pd.Descuento / 100.0)
        AS DECIMAL(12, 2)
    ) AS Importe
FROM dbo.PedidoDetalle AS pd;

The conversion sets the precision—the total number of digits—and the scale—the digits after the decimal separator—that we expect to display or store.

Format a date for exchange

SELECT
    p.IdPedido,
    CONVERT(CHAR(10), p.FechaPedido, 23) AS FechaISO
FROM dbo.Pedido AS p;

Style 23 produces aaaa-mm-dd. To sort, compare and calculate, it is advisable to keep the column as the date; the conversion to text should happen only when we need to present or exchange the value.

Detect values ​​that cannot be converted

SELECT
    d.ValorOriginal,
    TRY_CONVERT(DECIMAL(12, 2), d.ValorOriginal) AS ValorConvertido
FROM
(
    VALUES
        ('349.00'),
        ('1,250.50'),
        ('sin dato')
) AS d (ValorOriginal);

Values ​​that do not follow the expected format produce NULL instead of stopping the entire query. This allows us to locate them before modifying a column:

WHERE TRY_CONVERT(DECIMAL(12, 2), ValorOriginal) IS NULL
  AND ValorOriginal IS NOT NULL;

String with thousands separator may be format dependent and should not be assumed valid without a clear rule of origin.

Conversion failed and conversion not allowed

The TRY functions do not convert any type to any other. If SQL Server considers the conversion to be explicitly prohibited, it may still return an error. Its main use is in text values ​​with variable quality.

What should be checked

We must review length, precision, scale, and possible truncation. Truncation occurs when the destination cannot preserve the entire value. Converting a decimal to an integer loses the fraction; converting text to a shorter length may cut it off or fail, depending on the operation.

In filters and joins, it is preferable to cast the parameter or external value to the column type, not apply a function to each row of the column. In addition to being clearer, this can allow better access through indexes.