Validate conversions before modifying a column

A load table saves dates and quantities as text. Before converting the columns or moving the data to final tables we need to find the values that do not comply with the expected format.

What we need to distinguish

Conversion transforms a value into another type. The ISO yyyy-mm-dd format expresses year, month, and day in an order independent of local display conventions.

We will separate an absent value from one present but incompatible. In this example the date must use the ISO format yyyy-mm-dd and the quantity must be converted to a positive integer.

Query

/*
    Antes de ejecutar:
        Revisa la base activa, los nombres de objetos, los parámetros y los permisos.
        Adapta el ejemplo a tu modelo y pruébalo primero en un ambiente controlado.
*/

SET NOCOUNT ON;
GO
-- Consulta
WITH ValoresPreparados AS
(
    SELECT
        mi.IdMovimientoImportado,
        mi.FechaTexto,
        mi.CantidadTexto,
        NULLIF(TRIM(mi.FechaTexto), N'') AS FechaLimpia,
        NULLIF(TRIM(mi.CantidadTexto), N'') AS CantidadLimpia
    FROM dbo.MovimientoImportado AS mi
),
Conversiones AS
(
    SELECT
        vp.*,
        TRY_CONVERT(DATE, vp.FechaLimpia, 23) AS FechaConvertida,
        TRY_CONVERT(INT, vp.CantidadLimpia) AS CantidadConvertida
    FROM ValoresPreparados AS vp
)
SELECT
    c.IdMovimientoImportado,
    c.FechaTexto,
    c.FechaConvertida,
    c.CantidadTexto,
    c.CantidadConvertida,
    CASE
        WHEN c.FechaLimpia IS NULL THEN N'Fecha ausente'
        WHEN c.FechaConvertida IS NULL THEN N'Fecha incompatible'
        WHEN c.CantidadLimpia IS NULL THEN N'Cantidad ausente'
        WHEN c.CantidadConvertida IS NULL THEN N'Cantidad incompatible'
        WHEN c.CantidadConvertida <= 0 THEN N'Cantidad fuera de regla'
    END AS Motivo
FROM Conversiones AS c
WHERE c.FechaLimpia IS NULL
   OR c.FechaConvertida IS NULL
   OR c.CantidadLimpia IS NULL
   OR c.CantidadConvertida IS NULL
   OR c.CantidadConvertida <= 0
ORDER BY c.IdMovimientoImportado;
GO

Parameters or permissions

In DECIMAL, precision is the total number of digits and scale is the number after the decimal separator.

Change the destination type and the business rule. Style 23 requires a date like 2026-09-14; Using an explicit style prevents DATEFORMAT or the session language from interpreting an ambiguous value differently. For decimal numbers define precision and scale, for example DECIMAL(12, 2), and check the decimal separator used by the source. TRY_CAST is useful when you don’t need a style; TRY_CONVERT allows you to indicate it in conversions that support it. The query only reads the load table.

How to read the result

The query returns only rows that need attention. We keep the original text next to the converted value to distinguish a formatting issue from a functional rule, such as a quantity equal to zero. TRY_CONVERT produces NULL when an allowed conversion cannot be performed. That’s why we first transform empty strings into NULL and then keep separate flags for absence and incompatibility. To measure the scope before correcting, group the result by Reason or incorporate the same expressions into a summary query.

What this does establish

It allows you to identify values that are not converted under specific types and formats, as well as convertible values that violate an explicit rule.

What this still does not establish

That a conversion works does not prove that the data is correct. 2026-01-02 is a valid date, but the query doesn’t know if the source meant to represent February 1st. It also does not confirm that a type change is safe: indexes, constraints, SQL code, and applications may depend on the current definition.

Next reasonable check

Correct or reject the identified rows and rerun the validation. Before using ALTER COLUMN, review dependencies, range, precision, nullability, and a rollout and rollback strategy.

Search SQL Server