SQL Server - Date e Orari (Date and Time)
SQL Server
Tipi di dati
- L’orario UTC è il tempo coordinato universale (Coordinated Universal Time). È – a meno di piccole differenze – coincidente con il tempo medio di Greenwich GMT (Greenwich Mean Time).
- In SQL Server ci sono diversi tipi di dati per registrare date e orari e per definire campi o variabili. Ecco l’elenco con una breve spiegazione.
- Date: formato YYYY-MM-DD (anno, mese e giorno), per definire date.
- Time: formato hh:mm:ss,nnnnnnn (ore, minuti, secondi e frazione di secondi con 7 cifre decimali), per definire orari.
- Smalldatetime: formato YYYY-MM-DD hh:mm:ss (anno, mese, giorno, ore, minuti e secondi) per definire date e orari, sconsigliato.
- Datetime: formato YYYY-MM-DD hh:mm:ss,nnn (anno, mese, giorno, ore, minuti, secondi e frazione di secondi con 3 cifre decimali) per definire date e orari con tre cifre decimali, sconsigliato.
- Datetime2: formato YYYY-MM-DD hh:mm:ss,nnnnnnn (anno, mese, giorno, ore, minuti, secondi e frazione di secondi con 7 cifre decimali) per definire date e orari con sette cifre decimali, consigliato.
- Datetimeoffset: formato: YYYY-MM-DD hh:mm:ss,nnnnnnn [+|-]hh:mm (anno, mese, giorno, ore, minuti, secondi e frazione di secondi con 7 cifre decimali, offsest) per definire date e orari con sette cifre decimali, più l’offset rispetto all’orario UTC.
- Esempio.
- Si crea una tabella i cui campi sono definiti con tutti i tipi di date e orari appena presentati.
- Ecco lo script di creazione. Si presume l’esistenza del database ‘Test’. La tabella creata si chiama ‘DateAndTime’.
USE TESTGOCREATE TABLE [dbo].[DateAndTime]([f_date] [date] NULL,[f_time] [time](7) NULL,[f_smalldatetime] [smalldatetime] NULL,[f_datetime] [datetime] NULL,[f_datetime2] [datetime2](7) NULL,[f_datetimeoffset] [datetimeoffset](7) NULL)
- Script di inserimento di un record i cui campi sono impostati alla data e/o all’ora corrente. È lasciato al 'motore' del database il fatto di inserire la data e/o l’ora nel corretto formato accettato rispettivamente dai vari campi. La funzione SYSDATETIME() infatti restituisce la data e l’ora corrente come DateTime2, ma i campi che accettano solo una parte del DateTime2 sono valorizzati correttamente.
INSERT INTO [Test].[dbo].[DateAndTime]VALUES(SYSDATETIME(), SYSDATETIME(), SYSDATETIME(), SYSDATETIME(), SYSDATETIME(), SYSDATETIME())
- Query di selezione della riga appena inserita.
SELECT *FROM [Test].[dbo].[DateAndTime]
- Risultato (con testo riformattato per una migliore leggibilità):
f_date 2019-04-24f_time 22:02:01.6597086f_smalldatetime 2019-04-24 22:02:00f_datetime 2019-04-24 22:02:01.660f_datetime2 2019-04-24 22:02:01.6597086f_datetimeoffset 2019-04-24 22:02:01.6597086 +00:00
- Queste funzioni, non molto utilizzate, ritornano tutte un Datetime. Notare che la seconda funzione ritorna l’ora UTC (che non coincide con quella italiana).
SELECT GETDATE(),GETUTCDATE(),CURRENT_TIMESTAMP
- Risultato:
2019-04-25 14:48:14.700 2019-04-25 12:48:14.700 2019-04-25 14:48:14.700
- Queste funzioni, più utilizzate, ritornano rispettivamente un Datetime2 (le prime due) e un Datetimeoffset (la terza). Notare che la seconda funzione ritorna l’ora UTC (che non coincide con quella italiana).
SELECT SYSDATETIME(),SYSUTCDATETIME(),SYSDATETIMEOFFSET()
- Risultato:
2019-04-25 14:54:13.7407610 2019-04-25 12:54:13.7407610 2019-04-25 14:54:13.7407610 +02:00
ISDATE
- Verifica se la stringa passata come argomento è trasformabile in Date, Time o DateTime. Restituisce 1 (se è possibile) o 0 (se non è possibile).
- Notare che se si passa alla funzione un valore di tipo Datetime2, essa restituisce 0.
- Esempio:
SET DATEFORMAT ymdSELECT ISDATE('2000-12-16'),ISDATE('2000-12-16 04'), -- Non validoISDATE('2000-12-16 04:18'),ISDATE('2000-12-16 04:18:20'),ISDATE('2000-12-16 04:18:20.123'),ISDATE('2000-12-16 04:18:20.1234567'), -- Non validoISDATE('04'), -- Non validoISDATE('04:18'),ISDATE('04:18:20')
- Risultato:
1 0 1 1 1 0 0 1 1
- Notare l’impostazione del formato della data con l’istruzione SET DATEFORMAT (che imposta il formato solo per la connessione in corso!). La mia preferenza personale è con il formato anno-mese-giorno, mentre – si ricorda – l’installazione di SQL Server in italiano ha come impostazione giorno-mese-anno.
- Ecco un paio di istruzioni utili:
SELECT @@LANGUAGE
- Che restituisce:
Italiano
SELECT DATEFORMAT FROM sys.syslanguages WHERE name = @@LANGUAGE
- Che restituisce:
dmy
DAY, MONTH, YEAR
- Restituiscono rispettivamente il giorno, il mese e l’anno del Datetime passato come argomento.
- Esempio:
SELECT DAY(SYSDATETIME()),DAY('2000-12-16'),DAY('2000-12-16 04:18'),MONTH(SYSDATETIME()),MONTH('2000-12-16'),MONTH('2000-12-16 04:18'),YEAR(SYSDATETIME()),YEAR('2000-12-16'),YEAR('2000-12-16 04:18')
- Risultato:
25 16 16 4 12 12 2019 2000 2000
DATENAME, DATEPART
- Restituiscono rispettivamente una NVARCHAR e un INT con una parte di data. Il primo parametro specifica la parte di data voluta, il secondo è la data sulla quale estrarre la parte.
- Il primo parametro può assumere uno di questi valori (notare che per ogni valore ci sono più opzioni):
Valore DescrizioneYEAR / YY / YYYY annoQUARTER / QQ / QQ trimestre (1..4)MONTH / MM / M mese (1..12)DAYOFYEAR / DY / Y giorno dell’annoDAY / DD / D giorno (1..31)WEEK / WK /WW settimana (1..53)WEEKDAY / DW giorno della settimana (1=domenica..7=sabato)
- Esempio con date:
SELECT DATENAME(YEAR, '2000-12-16'),DATENAME(QUARTER, '2000-12-16'),DATENAME(MONTH, '2000-12-16'),DATENAME(DAYOFYEAR, '2000-12-16'),DATENAME(DAY, '2000-12-16'),DATENAME(WEEK, '2000-12-16'),DATENAME(WEEKDAY, '2000-12-16')SELECT DATEPART(YEAR, '2000-12-16'),DATEPART(QUARTER, '2000-12-16'),DATEPART(MONTH, '2000-12-16'),DATEPART(DAYOFYEAR, '2000-12-16'),DATEPART(DAY, '2000-12-16'),DATEPART(WEEK, '2000-12-16'),DATEPART(WEEKDAY, '2000-12-16')
- Risultato:
2000 4 dicembre 351 16 51 sabato2000 4 12 351 16 51 6
- Esempio con orari:
SELECT DATENAME(HOUR, '2000-12-16 04:18:42'),DATENAME(MINUTE, '2000-12-16 04:18:42'),DATENAME(SECOND, '2000-12-16 04:18:42'),DATENAME(MILLISECOND, '2000-12-16 04:18:42'),DATENAME(MICROSECOND, '2000-12-16 04:18:42'),DATENAME(NANOSECOND, '2000-12-16 04:18:42'),DATENAME(TZOFFSET, '2000-12-16 04:18:42')SELECT DATEPART(HOUR, '2000-12-16 04:18:42'),DATEPART(MINUTE, '2000-12-16 04:18:42'),DATEPART(SECOND, '2000-12-16 04:18:42'),DATEPART(MILLISECOND, '2000-12-16 04:18:42'),DATEPART(MICROSECOND, '2000-12-16 04:18:42'),DATEPART(NANOSECOND, '2000-12-16 04:18:42'),DATEPART(TZOFFSET, '2000-12-16 04:18:42')
- Risultato:
4 18 42 0 0 0 +00:004 18 42 0 0 0 0
DATEADD
- Restituisce un Datetime ottenuto sommando ad un Datetime una quantità (positiva o negativa) espressa in una qualsiasi unità appartenente alla tabella nel paragrafo relativo alle funzioni datename e datepart.
SET DATEFORMAT ymdSELECT DATEADD(DAY, 2, '2000-12-16 10:11:12'),DATEADD(DAY, -2, '2000-12-16 10:11:12')
- Risultato:
2000-12-18 10:11:12.000 2000-12-14 10:11:12.000
DATEDIFF
- Restituisce la differenza tra due Datetime espressa in una qualsiasi unità appartenente alla tabella nel paragrafo relativo alle funzioni datename e datepart.
SELECT DATEDIFF(DAY, '2000-12-16', '2019-04-25'),DATEDIFF(DAY, '2000-12-16', SYSDATETIME())
- Risultato:
6704 6704