Problema: Tengo una tabla con datos importados donde todas las columnas tienen tipos de datos de texto, aunque los datos reales están en otro formato. ¿Cómo puedo convertir estos datos al tipo de datos correcto?

Solución: En Microsoft SQL Server, hay dos funciones que te permiten convertir datos de un tipo de datos a otro: CAST y CONVERT. Ambas funciones se describen en el tutorial “Aprende cómo convertir datos con SQL CAST y SQL CONVERT”. En este tutorial, profundizaremos en la función CAST, que se puede utilizar en bases de datos SQL con scripts T-SQL, declaraciones SELECT y procedimientos almacenados.

Conversiones de tipos de datos utilizando la función CAST de SQL

La sintaxis de CAST en SQL Server es la siguiente: CAST(expresión AS tipo_de_dato). Por ejemplo, si queremos convertir un valor booleano del tipo de datos bit a un tipo de datos tiny int, podemos hacerlo con una expresión válida como esta:

DECLARE @mybit BIT = 1;
SELECT Test = CAST(@mybit AS TINYINT);

No todos los tipos de datos se pueden convertir a todos los tipos de datos posibles. Por ejemplo, no se puede convertir una fecha a un entero. En la documentación oficial, puedes encontrar una buena descripción general de las conversiones implícitas y explícitas permitidas.

Conversión a un tipo de datos numérico

Puedes convertir datos de texto a un tipo de datos numérico, como int, bigint, numeric, y así sucesivamente, siempre y cuando el texto represente realmente un número. Veamos algunos ejemplos de Transact-SQL:

WITH CTE_SampleData AS
(
    SELECT TestData = '123'
    UNION ALL
    SELECT '-456'
    UNION ALL
    SELECT '0'
    UNION ALL
    SELECT '+5123'
    UNION ALL
    SELECT NULL
)
SELECT Test = CAST(TestData AS INT)
FROM CTE_SampleData;

El resultado:

Test
-----------
123
-456
0
5123
NULL

Se permiten signos (+ o -), pero no se pueden combinar. Los símbolos de moneda también resultan en un error. Sorprendentemente, la conversión de un valor decimal a un entero también falla. Sin embargo, la conversión implícita puede funcionar, pero no en todos los casos. Si intentas convertir implícitamente un valor decimal a un entero, funcionará y el resultado será un valor truncado (se elimina todo lo que está después del punto decimal):

DECLARE @myInt INT;
SET @myInt = 100.923;
SELECT @myInt;

Pero si cambiamos la entrada a una cadena de texto, la conversión implícita fallará aunque los datos SQL se vean iguales. Si los datos de origen pueden contener valores decimales mezclados con valores enteros, es más seguro convertir todo al tipo de datos numérico o decimal:

SELECT CAST('100.123' AS NUMERIC(10,3));

Puede que te tiente usar la función ISNUMERIC para comprobar si los datos se pueden convertir, pero los resultados no siempre son confiables. Una mejor opción es utilizar TRY_CONVERT o TRY_CAST. Estas funciones intentan convertir la expresión al tipo de datos deseado. Si falla, se devuelve NULL en lugar de un error:

WITH CTE_SampleData AS
(
    SELECT TestData = '123'
    UNION ALL
    SELECT '-456'
    UNION ALL
    SELECT '€123'
    UNION ALL
    SELECT '0'
    UNION ALL
    SELECT '+-5123'
)
SELECT Test = TRY_CAST(TestData AS INT)
FROM CTE_SampleData;

El resultado:

Test
-----------
123
-456
NULL
0
NULL

Conversión a tipos de datos DateTime

Es posible convertir números al tipo de datos (small) datetime, pero no a otros tipos de datos de fecha, como datetime2, date, datetimeoffset, y así sucesivamente. La idea detrás de la conversión de números a un datetime es que la parte antes del punto decimal agrega días a la fecha 1900-01-01, y la parte después del punto decimal se agrega a la parte de tiempo del datetime. Por ejemplo, el siguiente número agrega exactamente 1 día y 1 segundo a 1900-01-01 00:00:00:

SELECT CAST(1.00001156 AS DATETIME);

También se pueden convertir tipos de datos de texto a un tipo de datos de fecha. Se pueden utilizar varios formatos:

SELECT Test = CAST('2021-12-21' AS DATETIME)
UNION ALL
SELECT CAST('01-01-2021' AS DATETIME)
UNION ALL
SELECT CAST('01-feb-2021 23:59:59' AS DATETIME)
UNION ALL
SELECT CAST('jan 12, 2021 07:12:50PM' AS DATETIME)
UNION ALL
SELECT CAST('04/11/2021 07:12AM' AS DATETIME)

Pueden surgir problemas si el formato es ambiguo. Por ejemplo, ¿’01-02-2021′ representa el primero de febrero (como en la mayoría de los países), o es más bien el segundo de enero (como hacen algunos países)? A diferencia de CONVERT, CAST no tiene un parámetro de estilo opcional. Puedes influir en el estilo de fecha utilizando el comando SET DATEFORMAT:

SET DATEFORMAT DMY;
SELECT CAST('01-02-2021' AS DATE); -- primero de feb

SET DATEFORMAT MDY;
SELECT CAST('01-02-2021' AS DATE); -- segundo de ene

Conversión a tipos de datos de cadena de caracteres

Puedes convertir casi cualquier cosa a un tipo de datos de cadena de caracteres. Por lo tanto, muchas veces puedes confiar en la conversión implícita. Sin embargo, si quieres especificar una longitud, la función CAST es útil. En el siguiente ejemplo, se convierten varias expresiones de diferentes tipos de datos al tipo de datos varchar. En lugar de dejar que SQL Server determine la longitud, la establecemos explícitamente utilizando CAST:

SELECT Test = CAST(132456 AS VARCHAR(20))
UNION ALL
SELECT CAST(GETDATE() AS VARCHAR(20))
UNION ALL
SELECT CAST('¡Hola MSSQLTips!' AS VARCHAR(20));

Cuando se convierte a un tipo de datos de cadena de caracteres, como (N)CHAR o (N)VARCHAR, es una buena práctica especificar siempre una longitud. Como puedes ver en la segunda línea del conjunto de resultados del ejemplo anterior, el motor de la base de datos ha elegido un formato para el valor datetime. Si quieres influir en esto, deberás utilizar las opciones SET, como se explica en la sección anterior. Cuando se trata de tipos de datos de fecha, la función CONVERT es una mejor opción que CAST.

Alternativas

La función STR se utiliza específicamente para convertir datos numéricos a datos de caracteres. Tiene la ventaja de poder especificar una longitud y el número de decimales después del punto decimal. El resultado siempre es varchar:

SELECT MyNumber = STR(123.456,6,2);

Otra función para convertir a datos de caracteres es la función FORMAT, que se introdujo en SQL Server 2012. Esta función acepta una opción de formato o una cultura, lo que la hace útil para convertir fechas. Un ejemplo:

SELECT Test = FORMAT(GETDATE(),'d','us');

Aquí convertimos una fecha al formato utilizado en Estados Unidos. El problema con FORMAT es que está basado en CLR, lo que significa que tiene un costo de rendimiento. Demostremos esto con un ejemplo en la base de datos de ejemplo AdventureWorks:

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT PerfTest = CONVERT(VARCHAR(20),[MovementDate],101)
FROM AdventureWorksDW2017.[dbo].[FactProductInventory];

SELECT PerfTest = FORMAT([MovementDate],'d','us')
FROM AdventureWorksDW2017.[dbo].[FactProductInventory];

Una consulta formatea una fecha con la función CONVERT, otra con la función FORMAT. La función FORMAT termina en mi máquina en 10 segundos (para 776,256 filas):

PerfTest
-----------------------
01/01/2008
01/01/2008
01/01/2008
01/01/2008
...

La función CONVERT termina en la mitad del tiempo mientras realiza exactamente la misma cantidad de lecturas lógicas:

PerfTest
-----------------------
2008-01-01
2008-01-01
2008-01-01
2008-01-01
...

Especialmente la diferencia en el tiempo de CPU es sorprendente: CONVERT utiliza solo el 6% de lo que necesita FORMAT. La función FORMAT de SQL puede ser útil, pero se recomienda utilizarla solo en conjuntos de datos pequeños.

Artículo actualizado por última vez: 2022-02-01