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 TEST
GO
CREATE 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-24
f_time           22:02:01.6597086
f_smalldatetime 2019-04-24 22:02:00
f_datetime       2019-04-24 22:02:01.660
f_datetime2      2019-04-24 22:02:01.6597086
f_datetimeoffset 2019-04-24 22:02:01.6597086 +00:00

Data e Ora corrente

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 ymd
SELECT ISDATE('2000-12-16'),
      ISDATE('2000-12-16 04'),               -- Non valido
      ISDATE('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 valido
      ISDATE('04'),                          -- Non valido
      ISDATE('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                                     Descrizione
YEAR / YY / YYYY                           anno
QUARTER / QQ / QQ                          trimestre (1..4)
MONTH / MM / M                             mese (1..12)
DAYOFYEAR / DY / Y                         giorno dell’anno
DAY / 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 sabato
2000 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:00
4 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 ymd
SELECT 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