CAST e CONVERT (Transact-SQL)
Essas funções convertem uma expressão de um tipo de dados em outro.
Sintaxe.
-- CAST Syntax: CAST ( expression AS data type [ ( length ) ] ) -- CONVERT Syntax: CONVERT ( data type [ ( length ) ] , expression [ , style ] )
Para ver a sintaxe do Transact-SQL para o SQL Server 2014 e versões anteriores, confira a Documentação das versões anteriores.
Argumentos.
data type O tipo de dados de destino. Isso inclui xml , bigint , e sql variant . Tipos de dados alias não podem ser usados.
length Um inteiro opcional que especifica o tamanho do tipo de dados de destino, para tipos de dados que permitem um comprimento especificado do usuário. O valor padrão é 30.
style Uma expressão de inteiro que especifica como a função CONVERT converterá expression . Para um valor de estilo NULL, NULL é retornado. data type determina o intervalo.
Tipos de retorno.
Retorna a expression convertida em data type .
Estilos de data e hora.
Para uma expression de tipo de dados de data ou hora, style pode ter um dos valores mostrados na tabela a seguir. Outros valores são processados como 0. A partir do SQL Server 2012 (11.x), os únicos estilos compatíveis, ao converter dos tipos de data e hora em datetimeoffset , são 0 ou 1. Todos os outros estilos de conversão retornam erro 9809.
O SQL Server dá suporte ao formato de data, em estilo árabe, com o algoritmo kuwaitiano.
Sem século (yy) ( 1 ) Com século (aaaa) Standard Entrada/saída ( 3 ) - 0 ou 100 ( 1, 2 ) Padrão para datetime e smalldatetime mês dd aaaa hh:miAM (ou PM) 1 101 EUA 1 = mm/dd/aa 101 = mm/dd/aaaa 2 102 ANSI 2 = aa.mm.dd 102 = aaaa.mm.dd 3 103 Britânico/francês 3 = dd/mm/aa 103 = dd/mm/aaaa 4 104 Alemão 4 = dd.mm.aa 104 = dd.mm.aaaa 5 105 Italiano 5 = dd-mm-aa 105 = dd-mm-aaaa 6 106 (1) - 6 = dd mês aa 106 = dd mês aaaa 7 107 (1) - 7 = Mês dd, aa 107 = Mês dd, aaaa 8 ou 24 108 - hh:mi:ss - 9 ou 109 ( 1, 2 ) Padrão + milissegundos mês dd aaaa hh:mi:ss:mmmAM (ou PM) 10 110 EUA 10 = mm-dd-aa 110 = mm-dd-aaaa 11 111 JAPÃO 11 = aa/mm/dd 111 = aaaa/mm/dd 12 112 ISO 12 = aammdd 112 = aaaammdd - 13 ou 113 ( 1, 2 ) Padrão Europa + milissegundos dd mês aaaa hh:mi:ss:mmm (24h) 14 114 - hh:mi:ss:mmm (24h) - 20 ou 120 ( 2 ) ODBC canônico aaaa-mm-dd hh:mi:ss (24h) - 21 ou 25 ou 121 ( 2 ) ODBC canônico (com milissegundos) padrão para hora, data, datetime2 e datetimeoffset aaaa-mm-dd hh:mi:ss.mmm (24h) 22 - EUA mm/dd/aa hh:mi:ss AM (ou PM) - 23 ISO8601 aaaa-mm-dd - 126 ( 4 ) ISO8601 aaaa-mm-ddThh:mi:ss.mmm (sem espaços)
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 ( 0 ou 100 , 9 ou 109 , 13 ou 113 , 20 ou 120 , 23 e 21 ou 25 ou 121 ) sempre retornam o século (aaaa).
3 Entrada quando é feita a conversão em datetime ; saída quando é feita a conversão em dados de caractere.
4 Criado para uso do XML. Para a conversão de datetime ou smalldatetime em dados de caractere, veja o formato de saída na tabela anterior.
5 Islâmico é um sistema de calendário com diversas variações. SQL Server usa o algoritmo kuwaitiano.
Por padrão, o SQL Server interpreta anos de dois dígitos com base em um ano de corte de 2049. Isso significa que o SQL Server interpreta o ano de dois dígitos 49 como 2049 e o ano de dois dígitos 50 como 1950. Muitos aplicativos cliente, como aqueles que se baseiam em objetos de Automação, usam um ano de corte de 2030. SQL Server fornece a opção de configuração de corte de ano de dois dígitos para alterar o ano de corte usado pelo SQL Server. Isso permite o tratamento consistente de datas. É recomendável especificar anos de quatro dígitos.
6 Apenas compatível na conversão de dados de caractere em datetime ou smalldatetime . Ao converter dados de caractere que representam componentes apenas de data ou apenas de hora 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.
7 Use o indicador de fuso horário opcional Z para facilitar o mapeamento de valores datetime XML que contêm informações de fuso horário para valores datetime do SQL Server que não contêm fuso horário. Z indica o fuso horário UTC-0. O deslocamento de HH:MM, na direção + ou -, indica outros fusos horários. Por exemplo: 2006-12-12T23:45:12-08:00 .
Ao converter smalldatetime em dados de caractere, os estilos que incluem segundos ou milissegundos mostram zeros nessas posições. Ao converter de valores datetime ou smalldatetime , use um tamanho de tipo de dados char ou varchar apropriado para truncar as partes de data indesejadas.
Ao converter dados de caractere em datetimeoffset , usando um estilo que inclui uma hora, um deslocamento de fuso horário é acrescentado ao resultado.
Estilos float e real.
Para uma expression de float ou real , style pode ter um dos valores mostrados na tabela a seguir. Outros valores são processados como 0.
Estilos money e smallmoney.
Para uma expression de money ou smallmoney , style pode ter um dos valores mostrados na tabela a seguir. Outros valores são processados como 0.
Estilos xml.
Para uma expression xml , style pode ter um dos valores mostrados na tabela a seguir. Outros valores são processados como 0.
Se for habilitado, o servidor poderá usar as informações a seguir fornecidas em um subconjunto de DTD interno, para executar operações de análise de não validação.
- Os padrões de atributos são aplicados - As referências a entidades internas são resolvidas e expandidas - A correção sintática do modelo de conteúdo DTD é verificada.
Estilos binários.
Para uma expression de binary(n) , char(n) , varbinary(n) ou varchar(n) , style pode ter um dos valores mostrados na tabela a seguir. Os valores de estilo que não estão listados na tabela retornarão um erro.
Se o tamanho da expressão convertida for maior que o tamanho do data type , o resultado será truncado à direita.
data type s de comprimento fixo maiores que o resultado convertido têm zeros adicionados à direita do resultado.
Um data type do tipo caractere exige uma expressão binária. Cada caractere binário é convertido em dois caracteres hexadecimais. Suponha que o tamanho da expressão convertida ultrapasse o tamanho do data type . Nesse caso, ela será truncada.
Para um data type de tipo de caractere de tamanho fixo, se o tamanho do resultado convertido for menor que o tamanho do data type , serão adicionados espaços à direita da expressão convertida, a fim de manter um número par de dígitos hexadecimais.
Conversões implícitas.
Conversões implícitas não exigem a especificação da função CAST nem a função CONVERT. Conversões explícitas exigem a especificação da função CAST ou da função CONVERT. A ilustração a seguir mostra todas as conversões de tipo de dados explícitas e implícitas permitidas para tipos de dados fornecidos pelo sistema SQL Server. Isso inclui bigint , sql variant e xml . Não há nenhuma conversão implícita na atribuição do tipo de dados sql variant , mas há uma conversão implícita em sql variant .
O Centro de Download da Microsoft disponibiliza este gráfico para download como um arquivo PNG.
O gráfico acima ilustra todas as conversões explícitas e implícitas permitidas em SQL Server, mas o tipo de dados resultante da conversão depende da operação que está sendo executada:
Para conversões explícitas, a própria instrução determina o tipo de dados resultante. Para conversões implícitas, instruções de atribuição, como definir o valor de uma variável ou inserir um valor em uma coluna, resultarão no tipo de dados que foi definido pela declaração de variável ou definição de coluna. Para operadores de comparação ou outras expressões, o tipo de dados resultante dependerá das regras da precedência de tipo de dados.
Um exemplo prático sobre os efeitos de precedência de tipo de dados em conversões pode ser visto posteriormente nesta seção.
Ao converter entre datetimeoffset e os tipos de caractere char , nchar , nvarchar e varchar , a parte do deslocamento de fuso horário convertido sempre deve ter dois dígitos para HH e MM. Por exemplo, -08:00.
Como os dados Unicode sempre usam um número par de bytes, tenha cuidado ao converter binary ou varbinary bidirecionalmente em tipos de dados Unicode compatíveis. Por exemplo, a conversão a seguir não retorna um valor hexadecimal igual a 41. Retorna um valor hexadecimal igual a 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary) . Para obter mais informações, consulte Suporte a ordenações e a Unicode.
Tipos de dados de valor grande.
Tipos de dados de valor grande têm o mesmo comportamento de conversão implícita e explícita de seus equivalentes menores, escpecificamente, os tipos de dados varchar , nvarchar e varbinary . No entanto, considere as seguintes diretrizes:
A conversão de image em varbinary(max) e vice-versa opera como uma conversão implícita, assim como as conversões entre text e varchar(max) e ntext e nvarchar(max) . A conversão de tipos de dados de valor grande, como varchar(max) , em um tipo de dados equivalente menor, como varchar , é uma conversão implícita, mas ocorrerá um truncamento se o tamanho do valor grande exceder o tamanho especificado do tipo de dados menor. A conversão de nvarchar , varbinary ou varchar em seus tipos de dados de valor grande correspondentes ocorre implicitamente. A conversão de tipo de dados sql variant em tipos de dados de 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 mais informações sobre a conversão do tipo de dados xml , consulte Criar instâncias de dados XML.
tipo de dados xml.
Ao converter o tipo de dados xml explícita ou implicitamente em um tipo de dados de cadeia de caracteres ou binários, o conteúdo do tipo de dados xml é serializado de acordo com um conjunto de regras definido. Para obter informações sobre essas regras, consulte Definir a serialização de dados XML. Para obter informações sobre como converter de outros tipos de dados no tipo de dados xml , consulte Criar instâncias de dados XML.
Tipos de dados text e image.
Os tipos de dados text e image não dão suporte a conversão automática de tipo de dados. Você pode converter explicitamente os dados text em dados de caractere, e os dados image em binary ou varbinary , mas o tamanho máximo é de 8.000 bytes. Se você tentar uma conversão incorreta, por exemplo, tentar a conversão de uma expressão de caractere que inclui letras em um int , o SQL Server retornará uma mensagem de erro.
Ordenação da saída.
Quando as funções CAST ou CONVERT produzem uma cadeia de caracteres e elas recebem uma entrada de cadeia de caracteres, a saída tem a mesma ordenação e o mesmo rótulo de ordenação da entrada. Se a entrada não for uma cadeia de caracteres, a saída terá a ordenação padrão do banco de dados e um rótulo de ordenação de padrão coercível. Para obter mais informações, consulte Precedência de ordenação (Transact-SQL).
Para atribuir uma ordenação 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.
Truncando e arredondando resultados.
Ao converter expressões de caractere ou binárias ( binary , char , nchar , nvarchar , varbinary ou varchar ) em uma expressão de um tipo de dados diferente, a operação de conversão pode truncar os dados de saída, apenas parcialmente exibir os dados de saída ou retornar um erro. Esses casos ocorrerão se o resultado for curto demais para ser exibido. As conversões em binary , char , nchar , nvarchar , varbinary ou varchar são truncadas, com exceção das conversões mostradas na tabela a seguir.
De tipo de dados Em tipo de dados Result int , smallint ou tinyint char * varchar * nchar E nvarchar E money , smallmoney , numeric , decimal , float ou real char E varchar E nchar E nvarchar E.
* = O tamanho do resultado é curto demais para ser exibido.
E = Um erro é retornado porque o comprimento de resultado é curto demais para ser exibido.
O SQL Server garante que apenas conversões de ida e volta, em outras palavras, conversões que convertem um tipo de dados original e, em seguida, novamente no tipo de dados original, geram 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));
Não construa valores binary e depois converta-os em um tipo de dados da categoria de tipo de dados numéricos. O SQL Server não garante que o resultado de uma conversão de tipo de dados decimal ou numeric em binary será igual entre as versões do SQL Server.
O exemplo a seguir mostra uma expressão resultante que é muito pequena para ser exibida.
USE AdventureWorks2012; GO SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS [Sick Leave] FROM HumanResources.Employee e JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID WHERE NOT e.BusinessEntityID > 5;
Este é o conjunto de resultados.
FirstName LastName Title Sick Leave --------- ------------- ------- --------` Ken Sanchez NULL * Terri Duffy NULL * Roberto Tamburello NULL * Rob Walters NULL * Gail Erickson Ms. * (5 row(s) affected)
Quando você converter tipos de dados que têm casas decimais diferentes, às vezes, o SQL Server retornará um valor de resultado truncado e, em outros momentos, retornará um valor arredondado. Esta tabela mostra o comportamento.
Por exemplo, os valores 10,6496 e -10,6496 podem ser truncados ou arredondados durante a conversão em tipos int ou numeric :
SELECT CAST(10.6496 AS INT) as trunc1, CAST(-10.6496 AS INT) as trunc2, CAST(10.6496 AS NUMERIC) as round1, CAST(-10.6496 AS NUMERIC) as round2;
Os resultados da consulta são mostrados na seguinte tabela:
trunc1 trunc2 round1 round2 10 -10 11 -11.
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, esta conversão retorna $10.3497 :
SELECT CAST(10.3496847 AS money);
SQL Server retorna uma mensagem de erro ao converter dados não numéricos char , nchar , nvarchar ou varchar em decimal , float , int , numeric . O SQL Server também retorna erro quando uma cadeia de caracteres vazia (" ") é convertida em numeric ou decimal .
Algumas conversões de datetime não são determinísticas.
Os estilos para os quais a conversão de cadeia de caracteres em datetime é não determinística são os seguintes:
Todos os estilos inferiores a 100 1 106 107 109 113 130.
1 Com exceção dos estilos 20 e 21.
Caracteres suplementares (pares alternativos)
Começando com SQL Server 2012 (11.x), ao usar ordenações SC (caracteres suplementares), uma operação CAST de nchar ou nvarchar em um tipo nchar ou nvarchar de tamanho menor não será truncada dentro de um par alternativo. Em vez disso, a operação é truncada antes do caractere suplementar. Por exemplo, o fragmento de código a seguir deixa @x que contém só 'ab' . Não há espaço suficiente para conter o caractere suplementar.
DECLARE @x NVARCHAR(10) = 'ab' + NCHAR(0x10000); SELECT CAST (@x AS NVARCHAR(3));
Ao usar ordenações SC, o comportamento de CONVERT é análogo ao comportamento de CAST . Para saber mais, confira Suporte a ordenações e a Unicode – caracteres suplementares.
Suporte de compatibilidade.
Nas versões anteriores do SQL Server, o estilo padrão das operações CAST e CONVERT nos tipos de dados time e datetime2 é 121, exceto quando um dos tipos é usado em uma expressão de coluna computada. Para colunas computadas, o estilo padrão é 0. Esse comportamento afeta as colunas computadas quando são criadas, usadas em consultas que envolvam parametrização automática ou usadas em definições de restrição.
No nível de compatibilidade 110 e posterior, as operações CAST e CONVERT nos tipos de dados time e datetime2 sempre têm 121 como o estilo padrão. Se uma consulta depender do comportamento antigo, use um nível de compatibilidade inferior a 110 ou especifique explicitamente o estilo 0 na consulta afetada.
Valor do nível de compatibilidade Estilo padrão para CAST e CONVERT 1 Estilo padrão para coluna computada 110 121 0 > = 110 121 121.
1 Exceto para colunas computadas.
A atualização do banco de dados para o nível de compatibilidade 110 e superior não alterará os dados de usuário que foram armazenados em disco. Você deve corrigir esses dados manualmente conforme apropriado. Por exemplo, se você usar SELECT INTO para criar uma tabela com base em uma fonte que contém uma expressão de coluna computada descrita acima, os dados (usando o estilo 0) serão armazenados, em vez da própria definição de coluna computada. Você precisa atualizar manualmente esses dados para que correspondam ao estilo 121.
Exemplos.
a. Usando CAST e CONVERT.
Cada exemplo recupera o nome dos produtos que têm um 3 no primeiro dígito de seu preço de lista e converte seus valores ListPrice em int .
-- Use CAST USE AdventureWorks2012; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CAST(ListPrice AS int) LIKE '33%'; GO -- Use CONVERT. USE AdventureWorks2012; GO SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice FROM Production.Product WHERE CONVERT(int, ListPrice) LIKE '33%'; GO.
Este é o conjunto de resultados. O conjunto de resultados de exemplo é o mesmo para CAST e CONVERT.
ProductName ListPrice ------------------------------ --------------------- LL Road Frame - Black, 58 337.22 LL Road Frame - Black, 60 337.22 LL Road Frame - Black, 62 337.22 LL Road Frame - Red, 44 337.22 LL Road Frame - Red, 48 337.22 LL Road Frame - Red, 52 337.22 LL Road Frame - Red, 58 337.22 LL Road Frame - Red, 60 337.22 LL Road Frame - Red, 62 337.22 LL Road Frame - Black, 44 337.22 LL Road Frame - Black, 48 337.22 LL Road Frame - Black, 52 337.22 Mountain-100 Black, 38 3374.99 Mountain-100 Black, 42 3374.99 Mountain-100 Black, 44 3374.99 Mountain-100 Black, 48 3374.99 HL Road Front Wheel 330.06 LL Touring Frame - Yellow, 62 333.42 LL Touring Frame - Blue, 50 333.42 LL Touring Frame - Blue, 54 333.42 LL Touring Frame - Blue, 58 333.42 LL Touring Frame - Blue, 62 333.42 LL Touring Frame - Yellow, 44 333.42 LL Touring Frame - Yellow, 50 333.42 LL Touring Frame - Yellow, 54 333.42 LL Touring Frame - Yellow, 58 333.42 LL Touring Frame - Blue, 44 333.42 HL Road Tire 32.60 (28 rows affected)
B. Usando CAST com operadores aritméticos.
Este exemplo calcula uma computação de coluna única ( Computed ) dividindo as vendas totais acumuladas no ano ( SalesYTD ) pelo percentual de comissão ( CommissionPCT ). Esse valor é arredondado para o próximo número inteiro e, em seguida, é convertido em um tipo de dados int .
USE AdventureWorks2012; GO SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS INT) AS Computed FROM Sales.SalesPerson WHERE CommissionPCT != 0; GO.
Este é o conjunto de resultados.
Computed ------ 379753754 346698349 257144242 176493899 281101272 0 301872549 212623750 298948202 250784119 239246890 101664220 124511336 97688107 (14 row(s) affected)
C. Usando CAST para concatenar.
Este exemplo concatena expressões de não caractere usando CAST. Ele usa o banco de dados AdventureWorksDW.
SELECT 'The list price is ' + CAST(ListPrice AS VARCHAR(12)) AS ListPrice FROM dbo.DimProduct WHERE ListPrice BETWEEN 350.00 AND 400.00;
Este é o 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.
D. Usando CAST para produzir texto mais legível.
Este exemplo usa CAST na lista SELECT para converter a coluna Name em uma coluna char(10) . Ele usa o banco de dados AdventureWorksDW.
SELECT DISTINCT CAST(EnglishProductName AS CHAR(10)) AS Name, ListPrice FROM dbo.DimProduct WHERE EnglishProductName LIKE 'Long-Sleeve Logo Jersey, M';
Este é o conjunto de resultados.
Name ListPrice ---------- --------- Long-Sleev 31.2437 Long-Sleev 32.4935 Long-Sleev 49.99.
E. Usando CAST com a cláusula LIKE.
Este exemplo converte os valores SalesYTD da coluna money no tipo de dados int e, em seguida, no tipo de dados char(20) , de modo que a cláusula LIKE possa usá-lo.
USE AdventureWorks2012; GO SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID FROM Person.Person AS p JOIN Sales.SalesPerson AS s ON p.BusinessEntityID = s.BusinessEntityID WHERE CAST(CAST(s.SalesYTD AS INT) AS char(20)) LIKE '2%'; GO.
Este é o conjunto de resultados.
FirstName LastName SalesYTD BusinessEntityID ---------------- ------------------- ---------------- ------------- Tsvi Reiter 2811012.7151 279 Syed Abbas 219088.8836 288 Rachel Valdez 2241204.0424 289 (3 row(s) affected)
F. Usando CONVERT ou CAST com XML com tipo.
Estes exemplos mostram o uso de CONVERT para converter dados em XML tipado, usando Tipo de dados e colunas XML (SQL Server).
Este exemplo converte uma cadeia de caracteres com espaço em branco, texto e marcação em XML tipado e remove todos os espaços em branco insignificantes (espaço em branco delimitador entre nós):
SELECT CONVERT(XML, ' ')
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):
SELECT CONVERT(XML, ' ', 1)
Este exemplo converte uma cadeia de caracteres com espaço branco, texto e marcação em XML com tipo:
SELECT CAST(' Carol Elliot ' AS XML)
Consulte Criar instâncias de dados XML para obter mais exemplos.
G. Usando CAST e CONVERT com dados datetime.
Começando com valores GETDATE() , este exemplo exibe a data e hora atual, usa CAST para alterar a data e hora atual para um tipo de dados de caractere e, em seguida, usa CONVERT para exibir a data e hora no formato ISO 8601 .
SELECT GETDATE() AS UnconvertedDateTime, CAST(GETDATE() AS NVARCHAR(30)) AS UsingCast, CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo ISO8601 ; GO.
Este é o 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)
Este exemplo é aproximadamente o oposto do exemplo anterior. Este exemplo exibe uma data e hora como dados de caracteres, usa CAST para alterar os dados de caractere no tipo de dados datetime e, em seguida, usa CONVERT para alterar os dados de caractere 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.
Este é o 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)
H. Usando CONVERT com dados binários e de caractere.
Estes exemplos mostram os resultados da conversão de dados binários 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];
Este é o conjunto de resultados.
Style 0, binary to character ---------------------------- Name (1 row(s) affected)
Este exemplo mostra que Style 1 pode impor o truncamento do resultado. Os caracteres 0x no conjunto de resultados forçam o truncamento.
SELECT CONVERT(CHAR(8), 0x4E616d65, 1) AS [Style 1, binary to character];
Este é o conjunto de resultados.
Style 1, binary to character ------------------------------ 0x4E616D (1 row(s) affected)
Este exemplo mostra que Style 2 não trunca o resultado porque o resultado não inclui os caracteres 0x.
SELECT CONVERT(CHAR(8), 0x4E616d65, 2) AS [Style 2, binary to character];
Este é o conjunto de resultados.
Style 2, binary to character ------------------------------ 4E616D65 (1 row(s) affected)
Converta o valor de caractere 'Name' em um valor binário.
SELECT CONVERT(BINARY(8), 'Name', 0) AS [Style 0, character to binary];
Este é o 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];
Este é o conjunto de resultados.
Style 1, character to binary ---------------------------- 0x4E616D65 (1 row(s) affected)
SELECT CONVERT(BINARY(4), '4E616D65', 2) AS [Style 2, character to binary];
Este é o conjunto de resultados.
Style 2, character to binary ---------------------------------- 0x4E616D65 (1 row(s) affected)
I. Convertendo os tipos de dados de data e hora.
Este exemplo mostra a conversão de tipos de dados de data, hora e datetime.
DECLARE @d1 DATE, @t1 TIME, @dt1 DATETIME; SET @d1 = GETDATE(); SET @t1 = GETDATE(); SET @dt1 = GETDATE(); SET @d1 = GETDATE(); -- When converting date to datetime the minutes portion becomes zero. SELECT @d1 AS [DATE], CAST (@d1 AS DATETIME) AS [date as datetime]; -- When converting time to datetime the date portion becomes zero -- which converts to January 1, 1900. SELECT @t1 AS [TIME], CAST (@t1 AS DATETIME) AS [time as datetime]; -- When converting datetime to date or time non-applicable portion is dropped. SELECT @dt1 AS [DATETIME], CAST (@dt1 AS DATE) AS [datetime as date], CAST (@dt1 AS TIME) AS [datetime as time];
J. Usar CONVERT com dados datetime em diferentes formatos.
Começando com valores GETDATE() , este exemplo usa CONVERT para exibir todos os estilos de data e hora na seção Estilos de data e hora deste artigo.
Nº do formato Consulta de exemplo Resultado de exemplo 0 SELECT CONVERT(NVARCHAR, GETDATE(), 0) Aug 23 2022 1:39PM 1 SELECT CONVERT(NVARCHAR, GETDATE(), 1) 08/23/19 2 SELECT CONVERT(NVARCHAR, GETDATE(), 2) 19.08.23 3 SELECT CONVERT(NVARCHAR, GETDATE(), 3) 23/08/19 4 SELECT CONVERT(NVARCHAR, GETDATE(), 4) 23.08.19 5 SELECT CONVERT(NVARCHAR, GETDATE(), 5) 23-08-19 6 SELECT CONVERT(NVARCHAR, GETDATE(), 6) 23 Aug 19 7 SELECT CONVERT(NVARCHAR, GETDATE(), 7) Aug 23, 19 8 ou 24 ou 108 SELECT CONVERT(NVARCHAR, GETDATE(), 8) 13:39:17 9 ou 109 SELECT CONVERT(NVARCHAR, GETDATE(), 9) Aug 23 2022 1:39:17:090PM 10 SELECT CONVERT(NVARCHAR, GETDATE(), 10) 08-23-19 11 SELECT CONVERT(NVARCHAR, GETDATE(), 11) 19/08/23 12 SELECT CONVERT(NVARCHAR, GETDATE(), 12) 190823 13 ou 113 SELECT CONVERT(NVARCHAR, GETDATE(), 13) 23 Aug 2022 13:39:17:090 14 ou 114 SELECT CONVERT(NVARCHAR, GETDATE(), 14) 13:39:17:090 20 ou 120 SELECT CONVERT(NVARCHAR, GETDATE(), 20) 2022-08-23 13:39:17 21 ou 25 ou 121 SELECT CONVERT(NVARCHAR, GETDATE(), 21) 2022-08-23 13:39:17.090 22 SELECT CONVERT(NVARCHAR, GETDATE(), 22) 08/23/19 1:39:17 PM 23 SELECT CONVERT(NVARCHAR, GETDATE(), 23) 2022-08-23 101 SELECT CONVERT(NVARCHAR, GETDATE(), 101) 08/23/2022 102 SELECT CONVERT(NVARCHAR, GETDATE(), 102) 2022.08.23 103 SELECT CONVERT(NVARCHAR, GETDATE(), 103) 23/08/2022 104 SELECT CONVERT(NVARCHAR, GETDATE(), 104) 23.08.2022 105 SELECT CONVERT(NVARCHAR, GETDATE(), 105) 23-08-2022 106 SELECT CONVERT(NVARCHAR, GETDATE(), 106) 23 Aug 2022 107 SELECT CONVERT(NVARCHAR, GETDATE(), 107) Aug 23, 2022 110 SELECT CONVERT(NVARCHAR, GETDATE(), 110) 08-23-2022 111 SELECT CONVERT(NVARCHAR, GETDATE(), 111) 2022/08/23 112 SELECT CONVERT(NVARCHAR, GETDATE(), 112) 20220823 113 SELECT CONVERT(NVARCHAR, GETDATE(), 113) 23 Aug 2022 13:39:17.090 120 SELECT CONVERT(NVARCHAR, GETDATE(), 120) 2022-08-23 13:39:17 121 SELECT CONVERT(NVARCHAR, GETDATE(), 121) 2022-08-23 13:39:17.090 126 SELECT CONVERT(NVARCHAR, GETDATE(), 126) 2022-08-23T13:39:17.09 127 SELECT CONVERT(NVARCHAR, GETDATE(), 127) 2022-08-23T13:39:17.09 130 SELECT CONVERT(NVARCHAR, GETDATE(), 130) 22 ذو الحجة 1440 1:39:17.090P 131 SELECT CONVERT(NVARCHAR, GETDATE(), 131) 22/12/1440 1:39:17.090PM.
K. Efeitos da precedência de tipo de dados em conversões permitidas.
O exemplo a seguir define uma variável do tipo VARCHAR, atribui um valor inteiro à variável e seleciona uma concatenação da variável com uma cadeia de caracteres.
DECLARE @string VARCHAR(10); SET @string = 1; SELECT @string + ' is a string.' AS Result.
Este é o conjunto de resultados.
Result ----------------------- 1 is a string.
O valor int de 1 foi convertido em um VARCHAR.
O exemplo mostra uma consulta semelhante, que usa uma variável em vez de:
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + ' is not a string.' AS Result.
Nesse caso, a instrução SELECT gerará o seguinte erro:
Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the varchar value ' is not a string.' to data type int.
Para avaliar a expressão @notastring + ' is not a string.' , o SQL Server segue as regras de precedência de tipo de dados para concluir a conversão implícita antes que o resultado da expressão possa ser calculado. Como int tem uma precedência mais alta do que VARCHAR, o SQL Server tenta converter a cadeia de caracteres em um inteiro e falha porque essa cadeia de caracteres não pode ser convertida em um inteiro.
Se fornecermos uma cadeia de caracteres que possa ser convertida, a instrução terá sucesso, conforme foi visto no exemplo a seguir:
DECLARE @notastring INT; SET @notastring = '1'; SELECT @notastring + '1'
Nesse caso, a cadeia de caracteres '1' pode ser convertida no valor inteiro 1; assim, a instrução SELECT retornará o valor 2. Quando os tipos de dados fornecidos forem inteiros, o operador + se tornará o operador matemático de adição, em vez de uma concatenação de cadeia de caracteres.
Exemplos: Azure Synapse Analytics e PDW (Analytics Platform System)
L. Usando CAST e CONVERT.
Este exemplo recupera o nome dos produtos que têm um 3 no primeiro dígito de seu preço de lista e converte o ListPrice desses produtos em int . Ele usa o banco de dados AdventureWorksDW2022 .
SELECT EnglishProductName AS ProductName, ListPrice FROM dbo.DimProduct WHERE CAST(ListPrice AS int) LIKE '3%';
Este exemplo mostra a mesma consulta, usando CONVERT em vez de CAST. Ele usa o banco de dados AdventureWorksDW2022 .
SELECT EnglishProductName AS ProductName, ListPrice FROM dbo.DimProduct WHERE CONVERT(INT, ListPrice) LIKE '3%';
M. Usando CAST com operadores aritméticos.
Este exemplo faz um cálculo de um valor de coluna única, dividindo o preço unitário do produto ( UnitPrice ) pelo percentual de desconto ( UnitPriceDiscountPct ). Esse resultado é então arredondado para o número inteiro mais próximo e, por fim, convertido em um tipo de dados int . Este exemplo usa o banco de dados AdventureWorksDW2022 .
SELECT ProductKey, UnitPrice,UnitPriceDiscountPct, CAST(ROUND (UnitPrice*UnitPriceDiscountPct,0) AS int) AS DiscountPrice FROM dbo.FactResellerSales WHERE SalesOrderNumber = 'SO47355' AND UnitPriceDiscountPct > .02;
Este é o conjunto de resultados.
ProductKey UnitPrice UnitPriceDiscountPct DiscountPrice ---------- --------- -------------------- ------------- 323 430.6445 0.05 22 213 18.5043 0.05 1 456 37.4950 0.10 4 456 37.4950 0.10 4 216 18.5043 0.05 1.
N. Usando CAST com a cláusula LIKE.
Este exemplo converte a coluna ListPrice money em um tipo int e, em seguida, em um tipo char(20) , de modo que a cláusula LIKE possa usá-lo. Este exemplo usa o banco de dados AdventureWorksDW2022 .
SELECT EnglishProductName AS Name, ListPrice FROM dbo.DimProduct WHERE CAST(CAST(ListPrice AS INT) AS CHAR(20)) LIKE '2%';
O. Usando CAST e CONVERT com dados datetime.
Este exemplo exibe a data e a hora atuais, usa CAST para alterar a data e a hora atuais em um tipo de dados de caractere e, por fim, usa CONVERT para exibir a data e a hora no formato ISO 8601. Este exemplo usa o banco de dados AdventureWorksDW2022 .
SELECT TOP(1) SYSDATETIME() AS UnconvertedDateTime, CAST(SYSDATETIME() AS NVARCHAR(30)) AS UsingCast, CONVERT(NVARCHAR(30), SYSDATETIME(), 126) AS UsingConvertTo ISO8601 FROM dbo.DimCustomer;
Este é o conjunto de resultados.
UnconvertedDateTime UsingCast UsingConvertTo ISO8601 --------------------- --------------------------- --------------------------- 07/20/2010 1:44:31 PM 2010-07-20 13:44:31.5879025 2010-07-20T13:44:31.5879025.
Este exemplo é o oposto aproximado do exemplo anterior. Este exemplo exibe uma data e hora como dados de caractere, usa CAST para alterar os dados de caractere no tipo de dados datetime e, em seguida, usa CONVERT para alterar os dados de caractere no tipo de dados datetime . Este exemplo usa o banco de dados AdventureWorksDW2022 .
SELECT TOP(1) '2010-07-25T13:50:38.544' AS UnconvertedText, CAST('2010-07-25T13:50:38.544' AS DATETIME) AS UsingCast, CONVERT(DATETIME, '2010-07-25T13:50:38.544', 126) AS UsingConvertFrom ISO8601 FROM dbo.DimCustomer;
Este é o conjunto de resultados.
UnconvertedText UsingCast UsingConvertFrom ISO8601 ----------------------- ----------------------- ------------------------ 2010-07-25T13:50:38.544 07/25/2010 1:50:38 PM 07/25/2010 1:50:38 PM.