CAST e CONVERT (Transact-SQL)

Data aggiornamento: 17 luglio 2006

Converte in modo esplicito un'espressione da un tipo di dati in un altro. Le funzioni CAST e CONVERT funzionano in modo simile.

Convenzioni della sintassi Transact-SQL

Sintassi

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])

Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Argomenti

• expression
  Qualsiasi espressione valida.

• data_type
  Tipo di dati di sistema di destinazione. Sono i tipi xml, bigint e sql_variant. Non è possibile utilizzare i tipi di dati alias. Per ulteriori informazioni sui tipi di dati disponibili, vedere Tipi di dati (Transact-SQL).

• length
  Parametro facoltativo per i tipi di dati nchar, nvarchar, char, varchar, binary o varbinary. Per CONVERT, se length viene omesso il valore predefinito è 30 caratteri.

• style
  Stile del formato di data utilizzato per convertire dati di tipo datetime o smalldatetime in dati di tipo carattere (tipi di dati nchar, nvarchar, char, varchar, nchar o nvarchar), per convertire i dati di tipo carattere con formati di data e ora conosciuti in dati di tipo datetime o smalldatetime oppure formato di stringa utilizzato per convertire dati di tipo float, real, money o smallmoney in dati di tipo carattere (tipi di dati nchar, nvarchar, char, varchar, nchar o nvarchar). Se style è NULL, anche il risultato restituito è NULL.

  [!NOTA] Gli stili trattati in questo argomento sono supportati da SQL Server nelle combinazioni con i tipi di dati di destinazione di CONVERT descritte. Gli altri stili e le altre combinazioni non sono supportate. Non utilizzare stili non supportati. L'utilizzo di stili o combinazioni di stili e tipi di dati di destinazione non supportate può restituire un errore o risultati non attendibili. Questi risultati potrebbero non essere supportati in tutte le release di SQL Server.

  SQL Server supporta il formato data in stile arabo tramite l'algoritmo Kuwaiti.

  Nella tabella seguente le due colonne a sinistra rappresentano i valori di style per la conversione di dati datetime o smalldatetime in dati di tipo carattere. Per ottenere l'anno a quattro cifre, in cui è indicato il secolo (aaaa), aggiungere il valore 100 a un valore dell'argomento style.

  Senza il secolo (aa) (1)	Con il secolo (aaaa)	Standard	Input/Output (3)
  -	0 o 100 (1,2)	Valore predefinito	mes gg aaaa hh:miAM (o PM)
  1	101	U.S.	mm/gg/aaaa
  2	102	ANSI	aa.mm.gg
  3	103	Inglese Regno Unito/Francese	gg/mm/aa
  4	104	Tedesco	gg.mm.aa
  5	105	Italiano	gg-mm-aa
  6	106(1)	-	gg mes aa
  7	107(1)	-	Mes gg, aa
  8	108	-	hh:mi:ss
  -	9 o 109 (1,2)	Valore predefinito + millisecondi	mes gg aaaa hh:mi:ss:mmmAM (o PM)
  10	110	USA	mm-gg-aa
  11	111	Giapponese	aa/mm/gg
  12	112	ISO	aammgg
  -	13 o 113 (1,2)	Valore predefinito Europa + millisecondi	gg mes aaaa hh:mi:ss:mmm(24h)
  14	114	-	hh:mi:ss:mmm(24h)
  -	20 o 120 (2)	ODBC canonico	aaaa-mm-gg hh:mi:ss(24h)
  -	21 o 121 (2)	ODBC canonico (con millisecondi)	aaaa-mm-gg hh:mi:ss.mmm(24h)
  -	126 (4)	ISO8601	aaaa-mm-ggThh:mi:ss.mmm (senza spazi)
  	127(6, 7)	ISO8601 con fuso orario Z.	aaaa-mm-ggThh:mi:ss.mmmZ (senza spazi)
  -	130 (1,2)	Hijri (5)	gg mes aaaa hh:mi:ss:mmmAM
  -	131 (2)	Hijri (5)	gg/mm/aa hh:mi:ss:mmmAM

  1 Questi valori di stile restituiscono risultati non deterministici. Include tutti gli stili (aa) (senza secolo) e un subset di stili (aaaa) (con il secolo).

  2 I valori predefiniti (style0 o 100, 9 o 109, 13 o 113, 20 o 120 e 21 o 121) restituiscono sempre il secolo (aaaa).

  3 Input quando viene eseguita la conversione nel tipo di dati datetime, output quando viene eseguita la conversione nel tipo di dati carattere.

  4 Progettato per l'utilizzo in XML. Per le conversioni di dati di tipo datetime o smalldatetime in dati di tipo carattere, il formato di output corrisponde a quello indicato nella tabella.

  5 Hijri è un sistema di calendario con diverse varianti. In SQL Server 2005 viene utilizzato l'algoritmo kuwaitiano.

  Importante:

  Per impostazione predefinita, in SQL Server gli anni nel formato a due cifre vengono interpretati in base all'anno di cambio data 2049, ovvero l'anno a due cifre 49 viene interpretato come 2049 e l'anno a due cifre 50 viene interpretato come 1950. Numerose applicazioni client, ad esempio quelle basate su oggetti di automazione, utilizzano il 2030 come anno di cambio data. In SQL Server è disponibile l'opzione di configurazione per l'anno di cambio data a due cifre, che consente di modificare l'anno di cambio data utilizzato in SQL Server per una gestione consistente delle date. È consigliabile specificare l'anno nel formato a quattro cifre.

  6 Supportato solo per il cast di dati dal tipo carattere al tipo di dati datetime o smalldatetime. In caso di cast dei dati di tipo carattere che rappresentano solo componenti di data o di ora al tipo di dati datetime o smalldatetime, il componente di ora non specificato viene impostato su 00.00.00,000 e il componente di data non specificato viene impostato su 01-01-1900.

  7L'indicatore di fuso orario facoltativo Z viene utilizzato per semplificare il mapping tra i valori datetime XML con informazioni sul fuso orario e i valori SQL Server datetime senza fuso orario. Z è l'indicatore per il fuso orario UTC-0. Gli altri fusi orari sono indicati con il valore di offset HH.MM nella direzione + o -. Esempio: 2006-12-12T23:45:12-08:00.

  Quando si convertono dati di tipo smalldatetime in dati di tipo carattere, gli stili che includono secondi o millisecondi visualizzano una serie di zeri. Quando si esegue la conversione di valori datetime o smalldatetime, è possibile troncare le parti della data non desiderate specificando la lunghezza appropriata per il tipo di dati char o varchar.

  Nella tabella seguente vengono descritti i valori dell'argomento style che è possibile utilizzare per la conversione di dati di tipo float o real in dati di tipo carattere.

  Valore	Output
  0 (predefinito)	Al massimo 6 cifre. Utilizzare questo valore nella notazione scientifica, quando è appropriato.
  1	Sempre 8 cifre. Utilizzare questo valore nella notazione scientifica.
  2	Sempre 16 cifre. Utilizzare questo valore nella notazione scientifica.

  [!NOTA] Se per la conversione da float e real viene specificato lo stile 126, l'output equivale rispettivamente al valore 2 e 1 di style.

  Nella tabella seguente vengono descritti i valori dell'argomento style che è possibile utilizzare per la conversione di dati di tipo money o smallmoney in dati di tipo carattere.

  Valore	Output
  0 (predefinito)	Nessun separatore delle migliaia a sinistra del separatore decimale e due cifre a destra del separatore decimale, ad esempio 4235,98.
  1	Separatore delle migliaia ogni tre cifre a sinistra del separatore decimale e due cifre a destra del separatore decimale, ad esempio 3.510,92.
  2	Nessun separatore delle migliaia a sinistra del separatore decimale e quattro cifre a destra del separatore decimale, ad esempio 4235,9819.

  [!NOTA] Se per la conversione da money o smallmoney a dati di tipo carattere viene specificato lo stile 126, l'output equivale al valore 2 di style.

  Nella tabella seguente vengono descritti i valori dell'argomento style che è possibile utilizzare per la conversione di input di tipo stringa in dati di tipo xml.

  Valore

Tipi restituiti

Restituisce lo stesso valore di data_type.

Osservazioni

Le conversioni implicite sono conversioni eseguite senza specificare la funzione CAST o CONVERT. Le conversioni esplicite sono conversioni per cui è necessario specificare la funzione CAST o CONVERT. Nella figura seguente vengono illustrate le conversioni di tipi di dati esplicite e implicite consentite per i tipi di dati di sistema di SQL Server 2005. Questi sono xml, bigint e sql_variant. Non è possibile eseguire una conversione implicita in un'assegnazione dal tipo di dati sql_variant, ma è possibile eseguire una conversione implicita verso il tipo di dati sql_variant.

[!NOTA] Poiché i dati Unicode utilizzano sempre un numero pari di byte, prestare attenzione nella conversione tra dati di tipo binary o varbinary e tipi di dati supportati da Unicode. La conversione seguente, ad esempio, non restituisce il valore esadecimale 41, bensì 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Tipi di dati per valori di grandi dimensioni

Per quanto riguarda le conversioni implicite ed esplicite, i tipi di dati per valori di grandi dimensioni si comportano come i tipi di dati per valori di dimensioni minori, in specifico i tipi di dati varchar, nvarchar e varbinary. Considerare, tuttavia, le linee guida seguenti:

• La conversione dal tipo di dati image al tipo di dati varbinary(max) e viceversa è una conversione implicita, così come sono conversioni implicite quelle tra i tipi di dati text e varchar(max), e ntext e nvarchar(max).
• La conversione da tipi di dati per valori di grandi dimensioni, ad esempio varchar(max), a tipi di dati per valori di dimensioni minori, ad esempio varchar, è una conversione implicita, ma si verifica un troncamento se le dimensioni del valore di grandi dimensioni sono eccessive rispetto alla lunghezza specificata per il tipo di dati di dimensioni minori.
• La conversione da varchar, nvarchar o varbinary nei corrispondenti tipi di dati per valori di grandi dimensioni viene eseguita in modo implicito.
• La conversione dal tipo di dati sql_variant in tipi di dati per valori di grandi dimensioni è una conversione esplicita.
• Non è possibile convertire i tipi di dati per valori di grandi dimensioni nel tipo di dati sql_variant.

Per informazioni sulla conversione di tipi definiti dall'utente del CLR di Microsoft .NET Framework, vedere Esecuzione di operazioni su tipi definiti dall'utente. Per ulteriori informazioni sulla conversione dal tipo di dati xml, vedere Generazione di istanze XML.

Tipo di dati XML

Quando si esegue il cast esplicito o implicito del tipo di dati xml a un tipo string o binary, il contenuto del tipo di dati xml viene serializzato in base ad alcune regole. Per informazioni su tali regole, vedere Serializzazione di dati XML. Per informazioni sul cast del tipo XML a un tipo CLR definito dall'utente, vedere Esecuzione di operazioni su tipi definiti dall'utente. Per informazioni sulla conversione di altri tipi di dati al tipo di dati xml, vedere Generazione di istanze XML.

Tipi di dati text e image

La conversione automatica del tipo di dati non è supportata per i tipi di dati text e image. È possibile convertire in modo esplicito dati di tipo text in dati di tipo carattere e dati di tipo image in dati di tipo binary o varbinary, ma la lunghezza massima è di 8000 byte. Se si tenta di eseguire una conversione non corretta, ad esempio se si tenta di convertire un'espressione di tipo carattere contenente lettere in un tipo int, SQL Server restituisce un messaggio di errore.

Regole di confronto per l'output

Quando sia l'output che l'input di CAST o CONVERT sono stringhe di caratteri, le regole di confronto e la relativa etichetta dell'output corrispondono alle regole di confronto e alla relativa etichetta dell'input. Se l'input non è una stringa di caratteri, all'output sono associate le regole di confronto predefinite del database e un'etichetta delle regole di confronto a cui possono essere assegnati valori predefiniti. Per ulteriori informazioni, vedere Precedenza delle regole di confronto (Transact-SQL).

Per assegnare all'output regole di confronto diverse, applicare la clausola COLLATE all'espressione risultante della funzione CAST o CONVERT. Ad esempio:

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

Troncamento e arrotondamento dei risultati

Quando si esegue la conversione di espressioni di tipo carattere o binarie (char, nchar, nvarchar, varchar, binary o varbinary) in un'espressione con un tipo di dati diverso, i dati possono venire troncati o visualizzati solo parzialmente oppure viene restituito un errore per segnalare che il risultato è troppo breve per essere visualizzato. I risultati delle conversioni in dati di tipo char, varchar, nchar, nvarchar, binary e varbinary vengono troncati, ad eccezione delle conversioni riportate nella tabella seguente.

* = Risultato di lunghezza insufficiente per essere visualizzato. E = Viene restituito un errore perché la lunghezza del risultato è insufficiente per la visualizzazione.

Nelle operazioni di conversione viene garantita la restituzione di valori uguali in versioni diverse di SQL Server solo quando un tipo di dati viene convertito in un altro tipo di dati e quindi riconvertito nel tipo di dati iniziale. Nell'esempio seguente viene illustrata una conversione di questo tipo:

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

[!NOTA] Evitare di costruire valori di tipo binary e di convertirli in un tipo di dati della categoria dei tipi numerici. SQL Server non garantisce che il risultato della conversione di un tipo di dati decimal o numeric nel tipo binary sia uguale in versioni diverse di SQL Server.

Nell'esempio seguente viene illustrata un'espressione troppo breve per essere visualizzata.

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

Set di risultati:

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

(5 row(s) affected)

Quando si convertono tipi di dati con un numero di cifre decimali diverso, in alcuni casi il valore risultante viene troncato e in altri viene arrotondato. Nella tabella seguente viene illustrato questo comportamento.

Il risultato, ad esempio, della conversione seguente è 10:

SELECT CAST(10.6496 AS int)

Se si esegue una conversione di tipi di dati in cui il tipo di destinazione prevede un numero di decimali minore rispetto al tipo di origine, il valore viene arrotondato. Il risultato, ad esempio, della conversione seguente è $10.3497:

SELECT CAST(10.3496847 AS money)

SQL Server restituisce un messaggio di errore se vengono convertiti dati non numerici di tipo char, nchar, varchar o nvarchar nei tipi int, float, numeric o decimal. SQL Server restituisce inoltre un errore se viene convertita una stringa vuota (" ") nel tipo numeric o decimal.

Conversione di dati stringa binari

Quando dati di tipo binary o varbinary vengono convertiti in dati di tipo carattere e dopo il carattere x viene specificato un numero di cifre dispari, SQL Server aggiunge uno 0 (zero) dopo il carattere x per ottenere un numero di cifre pari.

I dati binari includono i caratteri da 0 a 9 e da A a F (o da a a f) raggruppati a due a due. Le stringhe binarie devono essere precedute da 0x. Per immettere, ad esempio, FF, digitare 0xFF. Il valore massimo è un valore binario di 8000 byte, ognuno dei quali è FF. I tipi di dati binary non sono adatti per i dati esadecimali, mentre lo sono per gli schemi di bit. Le conversioni e i calcoli di numeri esadecimali archiviati come dati binari possono non essere affidabili.

Quando viene specificata la lunghezza di un tipo di dati binary, ogni coppia di caratteri equivale a un carattere. Una lunghezza pari a 10 indica che verranno immessi 10 gruppi di due caratteri.

Le stringhe binarie vuote, rappresentate da 0x, possono essere archiviate come dati binari.

Esempi

A. Utilizzo delle funzioni CAST e CONVERT

In ogni esempio vengono recuperati i nomi dei prodotti il cui prezzo contiene un 3 come prima cifra e i relativi valori di ListPrice vengono convertiti nel tipo 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

B. Utilizzo della funzione CAST con operatori aritmetici

Nell'esempio seguente viene eseguito il calcolo di una sola colonna (Computed) dividendo il totale delle vendite dell'anno in corso (SalesYTD) per la percentuale di commissione (CommissionPCT). Il risultato viene convertito in un tipo di dati int dopo l'arrotondamento al numero intero più prossimo.

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

Set di risultati:

Computed      
------ 
379753754
346698349
257144242
176493899
281101272
0
301872549
212623750
298948202
250784119
239246890
101664220
124511336
97688107

(14 row(s) affected)

C. Utilizzo della funzione CAST per il concatenamento

Nell'esempio seguente vengono concatenate espressioni non binarie e non di tipo carattere 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

Set di risultati:

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)

D. Utilizzo di CAST per migliorare la leggibilità del testo

Nell'esempio seguente viene utilizzata la funzione CAST nell'elenco di selezione per convertire la colonna Name in una colonna di 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

Set di risultati:

Name       UnitPrice
---------- ---------------------
Long-Sleev 31.2437
Long-Sleev 32.4935
Long-Sleev 49.99

(3 row(s) affected)

E. Utilizzo di CAST con la clausola LIKE

Nell'esempio seguente viene convertita la colonna SalesYTD di tipo money in una colonna di tipo int e quindi in una colonna di tipo char(20), utilizzabile con la clausola 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

Set di risultati:

FirstName        LastName            SalesYTD         SalesPersonID
---------------- ------------------- ---------------- -------------
Carol            Elliott             2811012.7151      279
Julie            Estes               219088.8836       288
Janeth           Esteves             2241204.0424      289

(3 row(s) affected)

F. Utilizzo di CONVERT o CAST con XML tipizzato

Nei diversi esempi seguenti viene illustrato l'utilizzo di CONVERT per eseguire conversioni in XML tipizzato tramite il tipo di dati xml.

In questo esempio una stringa con spazi vuoti, testo e codice viene convertita in XML tipizzato e vengono rimossi tutti gli spazi vuoti non significativi (spazi vuoti limite tra i nodi):

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

In questo esempio una stringa simile con spazi vuoti, testo e codice viene convertita in XML tipizzato e vengono mantenuti gli spazi vuoti non significativi (spazi vuoti limite tra i nodi):

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

In questo esempio viene eseguito il cast di una stringa con spazi vuoti, testo e codice in XML tipizzato:

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

Per ulteriori informazioni, vedere Generazione di istanze XML.

G. Utilizzo di CAST e CONVERT con dati datetime

Nell'esempio seguente vengono visualizzate la data e l'ora correnti, viene utilizzato CAST per modificarle in un tipo di dati character e quindi viene utilizzato CONVERT per visualizzarle nel formato ISO 8901.

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

Set di risultati:

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)

L'esempio seguente rappresenta all'incirca l'opposto dell'esempio precedente. Nell'esempio vengono visualizzate una data e un'ora come dati di tipo character, viene utilizzato CAST per modificare i dati character nel tipo di dati datetime e quindi viene utilizzato CONVERT per modificare i dati nel tipo di dati datetime.

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

Set di risultati:

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

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

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

(1 row(s) affected)

Vedere anche

Riferimento

SELECT (Transact-SQL)
Funzioni di sistema (Transact-SQL)

Altre risorse

Conversione del tipo di dati (Motore di database)
Formato ISO 8601
Scrittura di istruzioni Transact-SQL internazionali

Guida in linea e informazioni

Assistenza su SQL Server 2005

Cronologia modifiche