SQLShack
Introduzione
Un compito comune per i neofiti è quello di imparare come fare un SQL convertire data e lavorare per convertire la data in altri tipi di dati o convertire altri tipi di dati in data.
Qui in questo articolo spiegheremo come lavorare e convertire le date in diversi formati o viceversa.
Requisiti
- SQL Server installato. A partire da SQL Server 2008
Esempio
Il primo esempio sarà semplice, abbiamo una colonna varchar con una data in una tabella, ma abbiamo bisogno di convertire il varchar in data. Abbiamo bisogno di fare un SQL convertire data.
Ecco lo script per creare la tabella con i dati:
|
1
2
3
4
5
6
7
8
9
10
11
12
|
CREARE TABELLA .(
NOT NULL,
(100) NULL,
CONSTRAINT PRIMARY KEY CLUSTERED
(
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) SU
) SU
GO
INSERT . (, ) VALORI (1, N’02-03-2005′)
INSERT . (, ) VALORI (2, N’03-05-2006′)
INSERT . (, ) VALUES (3, N’04-05-2011′)
|
Vogliamo convertire la colonna data da nvarchar(100) a una data.
Per farlo, proveremo a modificare il design della tabella:
Proveremo a cambiare il Data Type in smalldatetime:
Riceverai il seguente messaggio di errore:
Il salvataggio delle modifiche non è consentito. Le modifiche che hai fatto richiedono che le seguenti tabelle siano eliminate e ricreate. Hai apportato modifiche a una tabella che non può essere ricreata o hai attivato l’opzione Impedisci il salvataggio delle modifiche che richiedono la ricreazione della tabella.
Per risolvere questo errore, in SSMS vai a Strumenti > menu Opzioni:
In Opzioni, vai a Designer >Tabella e Database Designer e deseleziona l’opzione Impedisci il salvataggio delle modifiche che richiedono la ricreazione della tabella:
Questa opzione disabiliterà l’opzione di impedire il salvataggio della ricreazione della tabella. Quando modifichi il tipo di dati della colonna, richiede la ricreazione della tabella.
Ora, puoi salvare il progetto e la tua tabella sarà convertita in data e la data di conversione SQL è completata:
Funzioni di conversione
T-SQL contiene funzioni per convertire tipi di dati. Useremo CAST e CONVERT per fare una conversione SQL dei dati.
Iniziamo prima con CAST:
Come convertire da varchar, nvarchar, char, nchar a sql date usando CAST
L’esempio seguente mostra come convertire caratteri in un tipo di data datetime usando la funzione CAST:
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CAST(@vardate AS datetime) AS dataconverted;
|
L’esempio dichiara una variabile chiamata vardate e poi questa variabile che è un varchar è convertita in datetime usando la funzione CAST.
Nota: Per maggiori informazioni sulla funzione CAST, fate riferimento a questo link: CAST e CONVERT (Transact-SQL)
Come fare una conversione SQL di data da varchar, nvarchar, char, nchar a data usando CONVERT
CONVERT è una funzione che può fare lo stesso di CAST nello scenario precedente.
|
1
2
|
declare @vardate varchar(100)=’03-04-2016′
select CONVERT(datetime, @vardate) as dataconverted
|
Il codice T-SQL sta facendo lo stesso del cast, ma sta usando la funzione CONVERT. Il vantaggio di CONVERT è che si può facilmente cambiare il formato della data usando l’argomento style.
Per esempio, se volete la data nel formato ISO, potete usare la seguente frase T-SQL:
|
1
|
select CONVERT(nvarchar(30),getdate(), 121) as isoformat
|
Come convertire la data sql in diversi formati in T-SQL
Il seguente esempio mostra come convertire il formato della data in diversi formati.
Per il formato Japananes:
|
1
|
select CONVERT(nvarchar(30),getdate(), 111) as Japanformat
|
Per il formato USA:
|
1
|
select CONVERT(nvarchar(30),getdate(), 110) as USAformat
|
Per formato ANSI:
|
1
|
select CONVERT(nvarchar(30),getdate(), 102) as ANSIformat
|
Per formato inglese:
|
1
|
select CONVERT(nvarchar(30),getdate(), 103) as Britishformat
|
Per il formato tedesco:
|
1
|
select CONVERT(nvarchar(30),getdate(), 104) as Germanformat
|
Per il formato italiano:
|
1
|
select CONVERT(nvarchar(30),getdate(), 105) as Italianformat
|
Per il formato europeo standard:
|
1
|
select CONVERT(nvarchar(30),getdate(), 113) as EuropeDefaultformat
|
Per ODBC Canonical:
|
1
|
select CONVERT(nvarchar(30),getdate(), 120) as ODBCCanonicalformat
|
Avete sempre la possibilità di usare la funzione FORMAT per ottenere la data nel formato che volete:
|
1
|
SELECT FORMAT( getdate(), ‘dd/MM/yy’)
|
FORMAT è più facile gestire le date e usare il formato che preferite, perché non è necessario conoscere lo stile. Tuttavia, nella mia esperienza vedo un sacco di codice che usa le funzioni CAST e CONVERT quindi, è meglio conoscerle.
Nota: Per ulteriori informazioni sulla funzione FORMAT, fare riferimento a questo link: FORMAT (Transact-SQL)
Problemi relativi alle operazioni di conversione della data in SQL
Quando si cerca di convertire in data non è sempre possibile. L’esempio seguente mostra un errore comune:
|
1
2
|
declare @vardate varchar(100)=’11242016′
select CONVERT(datetime, @vardate) as dataconverted
|
Il messaggio di errore è il seguente:
Msg 242, Level 16, State 3, Line 22
La conversione di un tipo di dati varchar in un tipo di dati datetime ha dato come risultato un valore fuori range.
Hai bisogno di separatori per la data come un “/”, un “.” o un “-“.
L’esempio seguente, modifica la stringa da 11242016 a 11-24-2016 e poi converte in data sql:
|
1
2
3
4
5
|
dichiarare @vardate varchar(100)=’11242016′
impostare @vardate= SUBSTRING(@vardate, 1, 2)+’-‘+ SUBSTRING(@vardate, 3, 2)+’-‘+SUBSTRING(@vardate, 5, 4)
select CONVERT(date, @vardate) as dataconverted
|
Usiamo la sottostringa per concatenare il “-” per usare un formato data accettabile e poi usiamo la funzione CONVERT per convertire i caratteri in data sql.
Tipi di dati di data
In SQL Server, ci sono diversi tipi di tipi di dati di data:
- Time restituisce ore, minuti, secondi e nanosecondi (hh:mm:ss.nnnnnnnn)
- Date restituisce anno, mesi e giorni (yyyy-mm-dd)
- Datetime restituisce dati con questo formato: YYYY-MM-DD hh:mm:ss
- Smalldatetime restituisce la data con questo formato: YYYY-MM-DD hh:mm:ss
- Datetime2 è simile a Datetime, ma ha più precisione (YYYY-MM-DD hh:mm:ss)
- Datetimeoffset ha la precisione di datetime2, ma è usato per i fusi orari in UTC
SQL converte la data in intero
Se si usa CONVERT o CAST per convertire un datetime in intero, restituirà il numero di giorni dal 1900 alla data fornita.
Per esempio, il seguente codice T-SQL mostrerà il numero di giorni dal 1900 ad oggi:
|
1
|
SELECT CONVERT(INT, GETDATE())
|
Potete anche convertire in numeri interi l’anno, i mesi, i giorni, ecc. di un valore datetime. Il seguente codice mostra come memorizzare in variabili intere il giorno, il mese e l’anno di un valore datetime:
|
1
2
3
4
5
|
declare @year int = year(getdate())
dichiarare @mese int = mese(getdate())
dichiarare @giorno int = giorno(getdate())
seleziona @anno come anno,@mese come mese,@giorno come giorno
|
Domande comuni su SQL convertire la data in SQL Server
Nota: Il seguente link contiene FAQ sulle funzioni e le date in SQL Server: FAQ sulle date in SQL Server
Conclusioni
In questo articolo, abbiamo imparato come fare una data di conversione SQL in SQL Server. Abbiamo imparato come modificare il tipo di dati in una tabella, come usare le funzioni CAST, CONVERT e FORMAT. Abbiamo anche imparato i diversi tipi di tipi di dati SQL.
- Autore
- Post recenti
Ha lavorato per il governo, compagnie petrolifere, siti web, riviste e università in tutto il mondo. Daniel parla anche regolarmente alle conferenze e ai blog di SQL Servers. Scrive materiali di formazione SQL Server per gli esami di certificazione.
Aiuta anche a tradurre gli articoli di SQLShack in spagnolo
Vedi tutti i post di Daniel Calbimonte
- Domande sulle interviste SQL – 3 settembre, 2020
- Certificazioni SQL – 7 agosto 2020
- Storia TSQL – 3 settembre 2019