CAST e CONVERT (Transact-SQL)

Quando expression é um tipo de dados de data ou hora, style pode ser um dos valores mostrados na tabela a seguir. Outros valores são processados como 0. O SQL Server suporta o formato de data em árabe usando o algoritmo kuwaitiano.

1 Esses valores de estilo retornam resultados não determinísticos. Incluem todos os estilos (aa) (sem século) e um subconjunto de estilos (aaaa) (com século).

2 Os valores padrão (style0 ou 100, 9 ou 109, 13 ou 113, 20 ou 120 e 21 ou 121) sempre retornam o século (aaaa).

3 Entrada quando você converte em datetime; saída quando você converte em dados de caracteres.

4 Criado para uso de XML. Para conversão de datetime ou smalldatetime em dados de caractere, o formato de saída é conforme descrito na tabela anterior.

5 Islâmico é um sistema de calendário com muitas variações. O SQL Server usa o algoritmo kuwaitiano.

Importante

Por padrão, o SQL Server interpreta anos de dois dígitos com base em um ano de corte de 2049. Isto é, o ano 49 de dois dígitos é interpretado como 2049 e o ano 50 de dois dígitos é interpretado como 1950. Muitos aplicativos cliente, como aqueles baseados em objetos de automação, usam um ano de corte de 2030. O SQL Server fornece a opção de configuração de corte de ano de dois dígitos que altera o ano de corte usado pelo SQL Server e permite tratamento consistente de datas. É recomendável especificar anos de quatro dígitos.

6 Tem suporte apenas ao converter dados de caracteres em datetime ou smalldatetime. Quando dados de caracteres que representam apenas componentes de data ou de hora são convertidos em tipos de dados datetime ou smalldatetime, o componente de hora não especificado é definido como 00:00:00.000 e o componente de data não especificado é definido como 1900-01-01.

7O indicador de fuso horário opcional, Z, é usado para facilitar o mapeamento de valores datetime XML que contêm informações de fuso horário em valores SQL Serverdatetime que não possuem fuso horário. Z é o indicador de fuso horário UTC-0. Outros fusos horários são indicados com deslocamento de HH:MM na direção + ou -. Por exemplo: 2006-12-12T23:45:12-08:00.

Quando dados são convertidos de smalldatetime em caracteres, os estilos que incluem segundos ou milissegundos mostram zeros nessas posições. É possível truncar partes não desejadas da data ao converter de valores datetime ou smalldatetime usando um comprimento de tipo de dados char ou varchar apropriado.

Quando você converter datetimeoffset de dados de caracteres com um estilo que inclui uma hora, um deslocamento de fuso horário é anexado ao resultado.

Quando expression for float ou real, style poderá ser um dos valores mostrados na tabela a seguir. Outros valores são processados como 0.

Quando expression for money ou smallmoney,style poderá ser um dos valores mostrados na tabela a seguir. Outros valores são processados como 0.

Quando expression for xml, style poderá ser um dos valores mostrados na tabela a seguir. Outros valores são processados como 0.

Valor	Saída
0 (padrão)	Use comportamento de análise padrão que descarta espaço em branco insignificante e não permite um subconjunto de DTD interno. Observação Ao converter em tipo de dados xml, o espaço em branco insignificante do SQL Server é tratado de maneira diferente no XML 1.0. Para obter mais informações, consulte Gerando instâncias XML.
1	Preserva espaço em branco insignificante. Essa configuração de estilo define o tratamento de xml:space padrão para se comportar da mesma forma como se xml:space="preserve" estivesse especificado.
2	Habilita o processamento de subconjunto de DTD interno limitado. Se habilitado, o servidor poderá usar as seguintes informações fornecidas em um subconjunto de DTD interno para executar operações de análise de não validação. Padrões de atributos são aplicados. Referências a entidades internas são resolvidas e expandidas. A exatidão sintática do modelo de conteúdo DTD será verificada. O analisador ignorará subconjuntos de DTD externos. Ele também não avalia a declaração XML para verificar se o atributo standalone está definido como yes ou no, mas analisa a instância XML como se ela fosse um documento autônomo.
3	Preserva espaço em branco insignificante e habilita processamento de subconjunto de DTD interno limitado.

Quando expression for binary(n), varbinary(n), char(n) ou varchar(n), style poderá ser um dos valores mostrados na tabela a seguir. Valores de estilo que não estão listados na tabela retornarão um erro.

Conversões implícitas são conversões que ocorrem sem especificar a função CAST ou CONVERT. Conversões explícitas são as conversões que exigem que CAST ou CONVERT sejam especificados. A ilustração a seguir mostra todas as conversões de tipos de dados explícitas e implícitas que são permitidas para tipos de dados fornecidos pelo sistema SQL Server. Os tipos incluem xml, bigint e sql_variant. Não há nenhuma conversão implícita na atribuição do tipo de dados sql_variant, mas há conversão implícita em sql_variant.

Ao converter entre datetimeoffset e os tipos de caracteres char, varchar, nchar e nvarchar a parte do deslocamento do fuso horário convertido sempre deve ter dois dígitos para HH e MM, por exemplo, -08:00.

Observação
Como dados Unicode sempre usam um número par de bytes, tenha cuidado ao converter binary ou varbinary de ou para tipos de dados Unicode com suporte. Por exemplo, a conversão a seguir não retorna um valor hexadecimal de 41. Ela retorna 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Tipos de dados de valor grande exibem o mesmo comportamento de conversão implícita e explícita que suas contrapartes menores, principalmente os tipos de dados varchar, nvarchar e varbinary. Porém, você deve considerar as seguintes diretrizes:

• Conversão de image em varbinary(max) e vice-versa é uma conversão implícita como também são as conversões entre text e varchar(max), e ntext e nvarchar(max).

• Conversão de tipos de dados de valor grande, como varchar(max), em um tipo de dados de contraparte menor, como varchar, é uma conversão implícita, mas ocorrerá truncamento se o valor grande for grande demais para o comprimento especificado do tipo de dados menor.

• A conversão de varchar, nvarchar ou varbinary em seus tipos de dados de valor grande correspondentes é executada implicitamente.

• A conversão de tipo de dados sql_variant em tipos de dados do valor grande é uma conversão explícita.

• Tipos de dados de valor grande não podem ser convertidos no tipo de dados sql_variant.

Para obter informações sobre como converter tipos CLR (Common Language Runtime) do Microsoft.NET Framework definidos pelo usuário, consulte Executando operações em tipos definidos pelo usuário. Para obter mais informações sobre como converter do tipo de dados xml, consulte Gerando instâncias XML.

Ao converter tipos de dados xml explícita ou implicitamente em um tipo de dados de cadeia de caracteres ou binário, o conteúdo do tipo de dados xml é serializado com base em um conjunto de regras. Para obter informações sobre essas regras, consulte Serialização de dados XML. Para obter informações sobre como converter de XML em um tipo de dados CLR definido pelo usuário, consulte Executando operações em tipos definidos pelo usuário. Para obter informações sobre como converter de outros tipos de dados no tipo de dados xml, consulte Gerando instâncias XML.

Não há suporte para a conversão automática de tipo de dados text e image. É possível converter explicitamente dados text em dados de caracteres e dados image em binary ou varbinary, mas o comprimento máximo é de 8000 bytes. Se você tentar uma conversão incorreta, como a conversão de uma expressão de caracteres que inclua letras em um int), o SQL Server exibirá uma mensagem de erro.

Quando a saída de CAST ou CONVERT for uma cadeia de caracteres e a entrada também for uma cadeia de caracteres, a saída terá o mesmo agrupamento e o mesmo rótulo de agrupamento que a entrada. Se a entrada não for uma cadeia de caracteres, a saída terá o agrupamento padrão do banco de dados e um rótulo de agrupamento de padrão coercível. Para obter mais informações, consulte Precedência de agrupamento (Transact-SQL).

Para atribuir um agrupamento diferente à saída, aplique a cláusula COLLATE à expressão do resultado da função CAST ou CONVERT. Por exemplo:

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

Ao converter expressões de caracteres ou binários (char, nchar, nvarchar, varchar, binary ou varbinary) em uma expressão de um tipo de dados diferente, os dados podem ser truncados, exibidos apenas parcialmente ou um erro é retornado porque o resultado é curto demais para ser exibido. Conversões em char, varchar, nchar, nvarchar, binary e varbinary são trucadas, com exceção das conversões mostradas na tabela a seguir.

* = O comprimento do resultado é curto demais para ser exibido. E = Erro retornado porque o comprimento do resultado é curto demais para ser exibido.

O SQL Server garante que apenas conversões de ida e volta, conversões que convertem um tipo de dados original e, em seguida, de volta para o tipo original, produzirão os mesmos valores de versão para versão. O exemplo a seguir mostra essa conversão de ida e volta:

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))

Observação
Não tente construir valores binary e, em seguida, convertê-los em um tipo de dados da categoria numérica. O SQL Server não garante que o resultado da conversão de um tipo de dados decimal ou numeric em binary será igual entre versões do SQL Server.

O exemplo a seguir mostra uma expressão resultante que é muito pequena para ser exibida.

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

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)

Ao converter tipos de dados que têm casas decimais diferentes, algumas vezes o valor resultante é truncado e em outras vezes ele é arredondado. A tabela a seguir mostra o comportamento.

Por exemplo, o resultado da seguinte conversão é 10:

SELECT CAST(10.6496 AS int)

Ao converter tipos de dados em que o tipo de dados de destino tem menos casas decimais do que o tipo de dados de origem, o valor é arredondado. Por exemplo, o resultado da seguinte conversão é $10.3497:

SELECT CAST(10.3496847 AS money)

O SQL Server retorna uma mensagem de erro quando dados não numéricos char, nchar, varchar ou nvarchar são convertidos em int, float, numeric ou decimal. O SQL Server também retorna um erro quando uma cadeia de caracteres vazia (" ") é convertida em numeric ou decimal.

No SQL Server 2000, conversões de cadeia de caracteres de data e hora são marcadas como deterministas. Porém, isso não é verdade para os estilos listados na tabela a seguir. Para esses estilos, as conversões dependem das configurações de idioma. O SQL Server 2005 e as versões posteriores marcam essas conversões como não determinísticas.

A tabela a seguir lista os estilos para os quais a conversão de cadeia de caracteres em datetime é não determinística.

1 Com exceção dos estilos 20 e 21

Cada exemplo recupera o nome dos produtos que têm um 3 no primeiro dígito de seu preço de lista e converte seu ListPrice em 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

O exemplo a seguir calcula uma única computação da coluna (Computed) dividindo as vendas totais acumuladas no ano (SalesYTD) pela porcentagem de comissão (CommissionPCT). Esse resultado é convertido em um tipo de dados int depois de ser arredondado para o número inteiro mais próximo.

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

Conjunto de resultados.

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

O exemplo a seguir concatena expressões de tipos não binários e não caracteres usando 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

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)

O exemplo a seguir usa CAST na lista de seleção para converter a coluna Name em uma coluna 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

Conjunto de resultados.

Name       UnitPrice

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

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

O exemplo a seguir converte a coluna moneySalesYTD em uma coluna int e, depois, em uma coluna char(20) para que ela possa ser usada com a 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

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 seguir são apresentados vários exemplos que mostram como usar CONVERT para converter em XML com tipo usando o tipo de dados xml.

Este exemplo converte uma cadeia de caracteres com espaço em branco, texto e marcação em XML com tipo e remove todos os espaços em branco insignificantes (espaço em branco delimitador entre nós):

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

Este exemplo converte uma cadeia de caracteres semelhante com espaço em branco, texto e marcação em XML com tipo e preserva todos os espaços em branco insignificantes (espaço em branco delimitador entre nós):

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

Este exemplo converte uma cadeia de caracteres com espaço branco, texto e marcação em XML com tipo:

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

Para obter mais exemplos, consulte Gerando instâncias XML.

O exemplo a seguir exibe a data e a hora atuais, usa CAST para alterar a data e a hora atuais em um tipo de dados de caracteres e, em seguida, usa CONVERT para exibir a data e a hora no formato ISO 8901.

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

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)

O exemplo a seguir é aproximadamente o oposto do exemplo anterior. O exemplo exibe uma data e hora como dados de caracteres, usa CAST para alterar os dados de caracteres no tipo de dados datetime e, em seguida, usa CONVERT para alterar os dados de caracteres no tipo de dados 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

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)

Os exemplos a seguir mostram os resultados da conversão de dados binário e de caractere usando estilos diferentes.

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

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'

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'

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'

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'

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'

Conjunto de resultados.

Style 2, character to binary

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

0x4E616D65

(1 row(s) affected)