CAST y CONVERT (Transact-SQL)

Cuando expression es un tipo de datos de fecha u hora, style puede ser uno de los valores que se muestran en la tabla siguiente. Otros valores se procesan como 0. SQL Server admite el formato de fecha en estilo árabe utilizando el algoritmo Kuwaiti.

1 Estos valores de estilo devuelven resultados no deterministas. Incluye todos los estilos (aa) (sin el siglo) y un subconjunto de estilos (aaaa) (con el siglo).

2 Los valores predeterminados (style0 o 100, 9 o 109, 13 o 113, 20 o 120 y 21 o 121) siempre devuelven el siglo (aaaa).

3 Entrada cuando se convierte en datetime; salida cuando se convierte en datos de caracteres.

4 Diseñado para usarse con XML. Para convertir datos datetime o smalldatetime en datos de caracteres, el formato de salida es el descrito en la tabla anterior.

5 Hijri es un sistema de calendario con varias variaciones. SQL Server usa el algoritmo Kuwaiti.

Importante

De forma predeterminada, SQL Server interpreta los años de dos dígitos según el año límite 2049. Es decir, el año 49 de dos dígitos se interpreta como 2049 y el año 50 de dos dígitos se interpreta como 1950. Muchas aplicaciones cliente, como las basadas en objetos de automatización, utilizan como límite el año 2030. SQL Server proporciona la opción de configuración Fecha límite de año de dos dígitos, que cambia el año límite utilizado por SQL Server y permite el tratamiento coherente de las fechas. Se recomienda especificar años de cuatro dígitos.

6 Sólo se admite en la conversión de datos de caracteres a datetime o smalldatetime. Cuando se convierten datos de caracteres que representan componentes de sólo fecha o sólo hora al tipo de datos datetime o smalldatetime, el componente de hora no especificado se establece en 00:00:00.000 y el componente de fecha no especificado se establece en 1900-01-01.

7El indicador opcional de zona horaria, Z, se usa para facilitar la asignación de valores XML de tipo datetime que contienen información de zona horaria a valores de tipo datetime de SQL Server que no tienen zona horaria. Z es el indicador para la zona horaria UTC-0. Las otras zonas horarias se indican con un desplazamiento de HH:MM en sentido + o -. Por ejemplo: 2006-12-12T23:45:12-08:00.

Cuando se convierten datos de caracteres de tipo smalldatetime, los estilos que incluyen segundos o milisegundos muestran ceros en dichas posiciones. Puede truncar las partes de la fecha no deseadas cuando convierta valores datetime o smalldatetime si utiliza una longitud apropiada en el tipo de datos char o varchar.

Cuando se convierte en datetimeoffset a partir de datos de caracteres con un estilo que incluye una hora, se anexa un ajuste de zona horaria al resultado.

Cuando expression es float o real, style puede ser uno de los valores que se muestran en la tabla siguiente. Otros valores se procesan como 0.

Cuando expression es money o smallmoney,style, puede ser uno de los valores que se muestran en la tabla siguiente. Otros valores se procesan como 0.

Cuando expression is xml, style puede ser uno de los valores que se muestran en la tabla siguiente. Otros valores se procesan como 0.

Valor	Resultado
0 (valor predeterminado)	Utiliza el comportamiento de análisis predeterminado que descarta los espacios en blanco insignificantes y no permite un subconjunto DTD interno. Nota Al convertir al tipo de datos xml, los espacios en blanco insignificantes de SQL Server se controlan de forma distinta que en XML 1.0. Para obtener más información, vea Generar instancias XML.
1	Conserva los espacios en blanco insignificantes. Esta configuración establece el control xml:space predeterminado de modo que se comporte igual que si se hubiera especificado xml:space="preserve".
2	Habilita el procesamiento limitado de subconjuntos DTD internos. Si está habilitado, el servidor puede utilizar la siguiente información proporcionada en un subconjunto DTD interno para realizar operaciones de análisis que no se validan. Se aplican los valores predeterminados de los atributos. Las referencias a entidades internas se resuelven y se amplían. Se comprobará la corrección sintáctica del modelo de contenido DTD. El analizador pasará por alto los subconjuntos DTD externos. Tampoco evalúa la declaración XML para ver si el atributo standalone está establecido en yes o no, pero analiza la instancia XML como si se tratase de un documento independiente.
3	Conserva los espacios en blanco insignificantes y habilita el procesamiento limitado de los subconjuntos DTD internos.

Cuando expression es binary(n), varbinary(n), char(n) o varchar(n), style puede ser uno de los valores que se muestran en la tabla siguiente. Los valores de estilos que no se enumeran en la tabla devuelven un error.

Las conversiones implícitas son aquellas conversiones que tienen lugar sin especificar las funciones CAST o CONVERT. Las conversiones explícitas son aquellas conversiones que requieren la especificación de las funciones CAST o CONVERT. En la siguiente ilustración se muestran todas las conversiones de tipos de datos explícitas e implícitas permitidas para los tipos de datos proporcionados por el sistema de SQL Server. Incluyen xml, bigint y sql_variant. No existe una conversión implícita en la asignación del tipo de datos sql_variant, pero sí hay una conversión implícita en sql_variant.

Al convertir entre datetimeoffset y los tipos de caracteres char, varchar, nchar y nvarchar la parte del ajuste de zona horaria convertida siempre debe tener dígitos dobles para HH y MM, por ejemplo -08:00.

Los tipos de datos de valor grande tienen el mismo comportamiento de conversión implícito y explícito que sus equivalentes más pequeños, especialmente los tipos de datos varchar, nvarchar y varbinary. No obstante, se deben tener en cuenta las siguientes directrices:

• La conversión de datos image en varbinary(max) y viceversa es una conversión implícita, al igual que las conversiones entre text y varchar(max) y entre ntext y nvarchar(max).

• La conversión de tipos de datos de valor grande, como varchar(max), en un tipo de datos equivalente más pequeño, como varchar, es una conversión implícita, aunque se producirá truncamiento si el valor grande es demasiado grande para la longitud especificada del tipo de datos más pequeño.

• La conversión de varchar, nvarchar o varbinary en sus tipos de datos correspondientes de valor grande se realiza de forma implícita.

• La conversión del tipo de datos sql_variant en los tipos de datos de valor grande es una conversión explícita.

• Los tipos de datos de valor grande no se pueden convertir en el tipo de datos sql_variant.

Para obtener información acerca de la conversión de tipos definidos por el usuario CLR (Common Language Runtime) de Microsoft.NET Framework, vea Realizar operaciones en tipos definidos por el usuario. Para obtener más información acerca de la conversión del tipo de datos xml, vea Generar instancias XML.

Cuando se convierte de forma explícita o implícita el tipo de datos xml en un tipo de datos de cadena o binario, el contenido del tipo de datos xml se serializa en función de un conjunto de reglas. Para obtener información sobre estas reglas, vea Serialización de datos XML. Para obtener información sobre la conversión del tipo XML al tipo definido por el usuario CLR, vea Realizar operaciones en tipos definidos por el usuario. Para obtener información sobre la conversión de otros tipos de datos al tipo de datos xml, vea Generar instancias XML.

No se admite la conversión automática de los tipos de datos text e image. Puede convertir explícitamente datos text en datos de caracteres y datos image en binary o varbinary, pero la longitud máxima es de 8000 bytes. Si intenta una conversión incorrecta, como la de una expresión de caracteres que incluye letras en un tipo int, SQL Server devuelve un mensaje de error.

Cuando la salida de CAST o CONVERT es una cadena de caracteres y la entrada es otra, la salida tiene la misma intercalación y etiqueta de intercalación que la entrada. Si la entrada no es una cadena de caracteres, la salida tiene la intercalación predeterminada de la base de datos y una etiqueta de intercalación coaccionable-predeterminada. Para obtener más información, vea Prioridad de intercalación (Transact-SQL).

Para asignar otra intercalación a la salida, aplique la cláusula COLLATE a la expresión de resultado de las funciones CAST o CONVERT. Por ejemplo:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

Al convertir expresiones de caracteres o binarias (char, nchar, nvarchar, varchar, binary o varbinary) en una expresión de un tipo de datos diferente, los datos se pueden truncar, se pueden presentar parcialmente o se puede devolver un error porque el resultado es demasiado corto para ser mostrado. Las conversiones en char, varchar, nchar, nvarchar, binary y varbinary se truncan, excepto aquellas que se muestran en la siguiente tabla.

* = Resultado demasiado corto para ser mostrado. E = Error devuelto porque el resultado es demasiado corto para ser mostrado.

SQL Server garantiza que sólo las conversiones circulares, las conversiones que convierten un tipo de datos en otro y después vuelven a convertirlo en el tipo de datos original, devolverán los mismos valores en versiones diferentes. En el siguiente ejemplo se muestra una conversión circular:

DECLARE @myval decimal (5, 2)
SET @myval = 193.57
SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))
-- Or, using CONVERT
SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

En el siguiente ejemplo se muestra una expresión resultante demasiado corta para ser mostrada.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, SUBSTRING(c.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'
FROM HumanResources.Employee e JOIN Person.Contact c ON e.EmployeeID = c. ContactID
WHERE NOT EmployeeID >5

Éste es el conjunto de resultados.

FirstName LastName       Title                Sick Leave
-------------- -------------  --------------------  -----------
Gustavo        Achong         Mr.                   *
Catherine      Abel           Ms.                   *
Kim            Abercrombie    Ms.                   *
Humberto       Acevedo        Sr.                   *
Pilar          Ackerman       Sra.                  *

(5 row(s) affected)

Al convertir tipos de datos que difieren en los decimales, algunas veces el valor resultante se trunca y otras se redondea. En la siguiente tabla se muestra el comportamiento.

Por ejemplo, el resultado de la siguiente conversión es 10:

SELECT CAST(10.6496 AS int)

Al convertir tipos de datos cuando el tipo de datos de destino tiene menos decimales que el tipo de datos de origen, el valor se redondea. Por ejemplo, el resultado de la siguiente conversión es $10.3497:

SELECT CAST(10.3496847 AS money)

SQL Server devuelve un mensaje de error cuando se convierten datos de tipo char, nchar, varchar o nvarchar no numéricos en datos de tipo int, float, numeric o decimal. SQL Server también devuelve un error cuando una cadena vacía (" ") se convierte en datos de tipo numeric o decimal.

En SQL Server 2000, las conversiones de cadena a fecha y hora se marcan como deterministas. Sin embargo, esto no se aplica a los estilos descritos en la siguiente tabla. Para estos estilos, las conversiones dependen de la configuración de idioma. SQL Server 2005 y versiones posteriores marcan estas conversiones como no deterministas.

En la siguiente tabla se muestran los estilos para los que la conversión de cadena a fecha y hora es no determinista.

1 Con la excepción de los estilos 20 y 21

En cada ejemplo se recupera el nombre de aquellos productos que tienen un 3 como primer dígito del precio y se convierte ListPrice en int.

-- Use CAST
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CAST(ListPrice AS int) LIKE '3%';
GO

-- Use CONVERT.
USE AdventureWorks;
GO
SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice
FROM Production.Product
WHERE CONVERT(int, ListPrice) LIKE '3%';
GO

En el siguiente ejemplo se calcula una única columna (Computed) mediante la división de las ventas anuales hasta la fecha (SalesYTD) entre el porcentaje de la comisión (CommissionPCT). El resultado se convierte en un tipo de datos int después de redondearlo al número entero más próximo.

USE AdventureWorks;
GO
SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'
FROM Sales.SalesPerson 
WHERE CommissionPCT != 0;
GO

Éste es el conjunto de resultados.

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

En el siguiente ejemplo se concatenan expresiones no binarias que no son de caracteres mediante CAST.

USE AdventureWorks;
GO
SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 350.00 AND 400.00;
GO

Éste es el conjunto de resultados.

ListPrice

------------------

The list price is 357.06

The list price is 364.09

The list price is 364.09

The list price is 364.09

The list price is 364.09

(5 row(s) affected)

En el siguiente ejemplo se utiliza CAST en la lista de selección para convertir la columna Name en una columna de tipo char(10).

USE AdventureWorks;
GO
SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice
FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID
WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';
GO

Éste es el conjunto de resultados.

Name       UnitPrice

---------- ---------------------

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

En el siguiente ejemplo se convierte la columna SalesYTD de tipo money en una de tipo int y, a continuación, en una de tipo char(20) para que se pueda utilizar con la cláusula LIKE.

USE AdventureWorks;
GO
SELECT p.FirstName, p.LastName, s.SalesYTD, s.SalesPersonID
FROM Person.Contact p JOIN Sales.SalesPerson s ON p.ContactID = s.SalesPersonID
WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';
GO

Éste es el conjunto de resultados.

FirstName        LastName            SalesYTD         SalesPersonID

---------------- ------------------- ---------------- -------------

Carol             Elliott             2811012.7151     279

Julie             Estes               219088.8836      288

Janeth            Esteves             2241204.0424     289

(3 row(s) affected)

A continuación se incluyen varios ejemplos en los que se muestra el uso de CONVERT para convertir a XML con tipo mediante el tipo de datos XML.

En este ejemplo se convierte una cadena con espacios en blanco, texto y marcado en XML con tipo y se quitan todos los espacios en blanco insignificantes (espacios en blanco de límite entre los nodos):

CONVERT(XML, '<root><child/></root>')

En este ejemplo se convierte una cadena similar con espacios en blanco, texto y marcado en XML con tipo y se conservan los espacios en blanco insignificantes (espacios en blanco de límite entre los nodos):

CONVERT(XML, '<root>          <child/>         </root>', 1)

En este ejemplo se convierte una cadena con espacios en blanco, texto y marcado en XML con tipo:

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

Para obtener más ejemplos, vea Generar instancias XML.

En el ejemplo siguiente se muestran la fecha y la hora actuales, se utiliza CAST para cambiarlas a un tipo de datos de caracteres y, a continuación, se utiliza CONVERT para mostrar la fecha y la hora en el formato ISO 8901.

SELECT 
   GETDATE() AS UnconvertedDateTime,
   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,
   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;
GO

Éste es el conjunto de resultados.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

----------------------- ------------------------------ ------------------------------

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

El siguiente ejemplo es lo opuesto, aproximadamente, del ejemplo anterior. En el ejemplo se muestra la fecha y la hora como datos de caracteres, se utiliza CAST para cambiar los datos de caracteres al tipo de datos datetime y, a continuación, se utiliza CONVERT para cambiar los datos de caracteres al tipo de datos datetime.

SELECT 
   '2006-04-25T15:50:59.997' AS UnconvertedText,
   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,
   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;
GO

Éste es el conjunto de resultados.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

----------------------- ----------------------- ------------------------

2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997

(1 row(s) affected)

Los ejemplos siguientes muestran los resultados de convertir datos binarios y de caracteres utilizando estilos diferentes.

--Convert the binary value 0x4E616d65 to a character value.
SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'

Éste es el conjunto de resultados.

Style 0, binary to character

----------------------------

Name

(1 row(s) affected)

--The following example shows how Style 1 can force the result
--to be truncated. The truncation is caused by
--including the characters 0x in the result.
SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'

Éste es el conjunto de resultados.

Style 1, binary to character

------------------------------

0x4E616D

(1 row(s) affected)

--The following example shows that Style 2 does not truncate the
--result because the characters 0x are not included in
--the result.
SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'

Éste es el conjunto de resultados.

Style 2, binary to character

------------------------------

4E616D65

(1 row(s) affected)

--Convert the character value 'Name' to a binary value.
SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, character to binary'

Éste es el conjunto de resultados.

Style 0, character to binary

----------------------------------

0x4E616D6500000000

(1 row(s) affected)

SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'

Éste es el conjunto de resultados.

Style 1, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)

SELECT CONVERT(binary(4), '4E616D65', 1) AS 'Style 2, character to binary'

Éste es el conjunto de resultados.

Style 2, character to binary

----------------------------------

0x4E616D65

(1 row(s) affected)