Pagina personale di:
Carlo Vecchio
appunti di C#, R, SQL Server, ASP.NET, algoritmi, numeri
Vai ai contenuti

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


© 2020 Carlo Vecchio
Torna ai contenuti