Utilizzo di dati relativi a data e ora
Nelle sezioni seguenti in questo argomento vengono fornite informazioni ed esempi sull'uso dei tipi di dati e delle funzioni di data e ora. Per una panoramica di tutti i tipi di dati e delle funzioni di data e ora Transact-SQL, vedere Funzioni e tipi di dati di data e ora (Transact-SQL).
Utilizzo di operatori con i tipi di dati di data e ora
Utilizzo dei formati di data e ora
Stringhe letterali di data e ora
Formato stringa senza separatori
Formato ISO 8601
Formati di data alfabetici
Formati di data numerici
Formati di ora
Formato data e ora ODBC
Conversione date, time, datetime2 e datetimeoffset
Conversione tra stringhe letterali e time(n), date, datetime2(n) e datetimeoffset(n)
Conversione tra tipi di dati di data e ora
Utilizzo di CAST e CONVERT con time, date, datetime2 e datetimeoffset
Argomenti dello stile per le funzioni CONVERT
Limitazioni relative a data e ora di SQL Server
Compatibilità con le versioni precedenti dei client legacy
Esempi
Utilizzo di operatori con i tipi di dati di data e ora
Gli operatori relazionali (<, <=, >, >=, <>), gli operatori di confronto (=, <, <=, >, >=, <>, !<, !>), gli operatori logici e i predicati booleani (IS NULL, IS NOT NULL, IN, BETWEEN, EXISTS, NOT EXISTS e LIKE) sono supportati per tutti i tipi di dati date e time.
Operatori aritmetici di data e ora
Utilizzo dei formati di data e ora
I formati del valore letterale stringa influiscono sulla presentazione di dati agli utenti nelle applicazioni, ma non il formato di archiviazione integer sottostante in SQL Server. Tuttavia, SQL Server potrebbe interpretare un valore di data in un formato del valore letterale stringa, inserito da un'applicazione o un utente per l'archiviazione o per una funzione di data, come date diverse. L'interpretazione dipende dalla combinazione di impostazioni di formato del valore letterale stringa, tipo di dati e impostazioni di runtime SET DATEFORMAT, SET LANGUAGE e dell'opzione di lingua predefinita.
Queste impostazioni non influiscono su determinati formati del valore letterale stringa. Considerare la possibilità di utilizzare un formato che non dipende da queste impostazioni, a meno che queste impostazioni non siano corrette per il formato. Il formato ISO 8601 non dipende da queste impostazioni ed è uno standard internazionale. Transact-SQL, che utilizza formati del valore letterale stringa dipendenti dalle impostazioni del sistema, è meno portabile.
Per scoprire il formato predefinito per il valore letterale stringa per i client legacy, vedere l'argomento per ogni tipo di dati di data e ora. Per una panoramica di tutti i tipi di dati e delle funzioni di data e ora Transact-SQL, vedere Funzioni e tipi di dati di data e ora (Transact-SQL).
Il formato di data ydm non è supportato per i tipi di dati date, datetime2 e datetimeoffset. Viene generato un errore di runtime.
Stringhe letterali di data e ora
Nella tabella seguente sono elencati formati di stringa di data e ora diversi. Si consiglia di utilizzare i formati di data e ora multilinguaggio e che non dipendono da DATEFORMAT. I formati ISO 8601 '1998-02-23T14:23:05' e '1998-02-23T14:23:05 -08:00' sono gli unici formati considerati standard internazionali. Sono multilinguaggio e non dipendono da DATEFORMAT o dal linguaggio predefinito dell'account di accesso.
Parte di data/ora |
Tipo formato |
Esempio formato |
Può essere combinato con gli altri formati |
Dipendente da DATEFORMAT |
Multilinguaggio |
|---|---|---|---|---|---|
Data |
Non separato ISO 8601 |
'19980223' |
Sì |
No |
Sì |
Data |
Espressione numerica |
'02/23/1998' |
Sì |
Sì |
No (DATEFORMAT) |
Data |
Standard numerico ISO 8601 |
'1998-02-23' |
Sì |
No |
No |
Data |
Espressione alfabetica |
'23 febbraio 1998' |
Sì |
No |
No (mese o abbreviazione mese) |
Data |
Data ODBC |
{d '1998-02-23'} |
No |
No |
Sì |
Ora |
Ora ISO 8601 |
'14:23:05' '10:00:00.123456' |
Sì |
No |
Sì |
Ora |
Ora ODBC |
{t '14:23:05'} |
No |
No |
Sì |
Data-ora |
Data-ora ODBC |
{ts '1998-02-23 14:23:05'} |
No |
No |
Sì |
Data-ora |
ISO 8601 |
'1998-02-23T14:23:05' '1998-02-23T14:23:05 -08:00' |
No |
No |
Sì date, datetime2, datetimeoffset. |
Data-ora |
Standard ANSI SQL |
'1998-02-23 14:23:05' '1998-02-23 14:23:05 -08:00' |
No |
No (datetime2, datetimeoffset) Sì (datetime) |
Sì date, datetime2, datetimeoffset. |
Data-ora |
Combinazione di data e ora |
'19980223 14:23:05' '02/23/1998 2:23:05 PM' '1998-02-23 10:00:00.123' '23 Feb 1998 14:23:05' |
No |
Sì (parte relativa alla data) |
No |
TimeZone |
Formati TimeZone |
'+12:00' '01:00' '-08:00' 'Z' |
Sì |
No |
Sì |
Nelle istruzioni seguenti vengono illustrati gli effetti delle impostazioni SET LANGUAGE e SET DATEFORMAT.
DECLARE @Today date = '12/1/2003';
DECLARE @MyTime time = '12:30:22';
DECLARE @MyDatetimeoffset datetimeoffset = '12/1/2003 12:30:22 -5:00';
SET LANGUAGE Italian
-- Returns: Changed language setting to Italiano.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: dicembre
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: dic 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
SET LANGUAGE us_english;
-- Returns: Changed language setting to us_english.
SELECT DATENAME(month, @Today) AS 'Month Name';
-- Returns: December
SELECT CONVERT(VARCHAR(30), @MyDatetimeoffset) AS 'DATETIMEOFFSET';
-- Returns: Dec 1 2003 12:30PM -05:00
SELECT DATENAME(hour, @MyTime) As 'Hour Name';
-- Returns: 12
GO
-- Set date format to month, day, year.
SET DATEFORMAT mdy;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
-- Set date format to year, day, month.
SET DATEFORMAT ydm;
GO
DECLARE @datevar datetimeoffset = '1998/31/12 12:30:22 -05:00';
SELECT @datevar AS DateVar;
-- Returns: Msg 241, Conversion failed when converting
-- date and/or time from character string.
GO
-- Set date format to year, month, day.
SET DATEFORMAT ymd;
GO
DECLARE @datevar date = '12/31/1998';
SELECT @datevar AS DateVar;
-- Returns: 1998-12-31
GO
Formato stringa senza separatori
È possibile specificare i dati relativi alla data nel formato di stringa non separata. È possibile specificare i dati relativi alla data utilizzando quattro, sei o otto cifre, una stringa vuota o un valore time senza un valore di data.
L'impostazione della sessione SET DATEFORMAT non viene applicata alle voci di data numeriche, ad esempio le voci numeriche prive di separatori. Le stringhe a sei o otto cifre vengono sempre interpretate come ymd. Il mese e il giorno devono sempre essere rappresentati da due cifre.
Il formato stringa senza separatori valido è il seguente: [19]960415 [19]960415
Una stringa composta solo da quattro cifre viene interpretata come anno. Il mese e la data vengono impostate su 1 gennaio. Quando si specificano solo quattro cifre, è necessario includere il secolo.
Formato ISO 8601
La data ISO 8601 con formato dell'ora viene visualizzata come segue:
YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Coordinated Universal Time)
T indica l'inizio della parte dell'ora del valore datetime.
Per utilizzare il formato ISO 8601, è necessario specificare ogni elemento in tale formato. Include T, i due punti (:), il + o - e i punti (.). Le parentesi indicano che i secondi frazionari o i componenti della differenza del fuso orario sono facoltativi.
Il componente dell'ora viene specificato nel formato 24 ore.
Il vantaggio dell'utilizzo del formato ISO 8601 consiste nel fatto che è uno standard internazionale. Inoltre, i valori di data e ora specificati in tale formato non presentano ambiguità. Su questo formato non influiscono le impostazioni SET LANGUAGE, SET DATEFORMAT né quelle della lingua predefinita dell'account di accesso.
Di seguito sono riportati due esempi di valori data-ora specificati nel formato ISO 8601:
2004-05-23T14:25:10
2004-05-23T14:25:10.1234567+07:00
Formati di data alfabetici
È possibile specificare un mese come nome, ad esempio, April o l'abbreviazione Apr in inglese. Questi devono essere specificati nell'impostazione LANGUAGE della sessione, ad esempio, avril o avr in francese. Le virgole sono facoltative e l'uso delle maiuscole è ignorato.
Di seguito vengono riportate alcune linee guida per l'utilizzo dei formati di data alfabetici:
Racchiudere data e ora tra virgolette singole (').
Se si specificano solo le ultime due cifre dell'anno, i valori minori delle ultime due cifre del valore impostato nell'opzione di configurazione Cambio data per anno a due cifre vengono interpretati come appartenenti allo stesso secolo dell'anno di cambio data. I valori maggiori o uguali al valore di questa opzione vengono interpretati come appartenenti al secolo che precede l'anno di cambio data. Se ad esempio il valore di Cambio data per anno a due cifre è 2050 (impostazione predefinita), 25 è interpretato come 2025 mentre 50 è interpretato come 1950. Per evitare ambiguità, esprimere gli anni nel formato a quattro cifre.
Se manca il giorno, viene inserito il primo giorno del mese.
Quando si specifica il mese in formato alfabetico, l'impostazione SET DATEFORMAT della sessione non viene applicata.
I seguenti formati sono formati alfabetici validi per i dati relativi alla data di SQL Server. I caratteri inclusi tra parentesi sono facoltativi.
Apr[ile] [15][,] 1996
Apr[ile] 15[,] [19]96
Apr[ile] 1996 [15]
[15] Apr[ile][,] 1996
15 Apr[ile][,][19]96
15 [19]96 apr[ile]
[15] 1996 apr[ile]
1996 APR[ILE] [15]
1996 [15] APR[ILE]
Formati di data numerici
È possibile specificare i dati relativi alla data specificando un mese in formato numerico. Ad esempio, 5/20/97 rappresenta il ventesimo giorno di maggio 1997. Quando si utilizza un formato data numerico, specificare l'anno, il mese e il giorno in una stringa utilizzando come separatori la barra (/), il trattino (-) o il punto (.). Il formato corretto è il seguente:
numero separatore numero separatore numero [ora] [ora]
I formati numerici validi sono i seguenti:
[0]4/15/[19]96 -- (mdy)
[0]4-15-[19]96 -- (mdy)
[0]4.15.[19]96 -- (mdy)
[0]4/[19]96/15 -- (myd)
15/[0]4/[19]96 -- (dmy)
15/[19]96/[0]4 -- (dym)
[19]96/15/[0]4 -- (ydm)
[19]96/[0]4/15 -- (ymd)
L'impostazione DATEFORMAT della lingua predefinita per una sessione viene definita dalla lingua predefinita per l'account di accesso, un'istruzione SET LANGUAGE o un'istruzione SET DATEFORMAT. Quando la lingua viene impostata su us_english dall'accesso predefinito o dall'istruzione SET LANGUAGE, l'ordine predefinito di visualizzazione della data è mdy.
È possibile modificare il formato di data utilizzando l'istruzione SET DATEFORMAT. L'impostazione dell'istruzione SET DATEFORMAT determina la modalità di interpretazione dei valori di data. Se l'ordine non corrisponde all'impostazione, i valori non vengono interpretati come date perché compresi nell'intervallo valido, oppure non vengono interpretati correttamente. Ad esempio, la data 12/10/08 può essere interpretata in sei modi diversi a seconda dell'impostazione di DATEFORMAT. Un anno composto da quattro cifre viene interpretato come anno.
Formati di ora
In SQL Server vengono riconosciuti i formati seguenti per i dati relativi all'ora. Tutti i formati devono essere racchiusi tra virgolette singole (').
14:30
14:30[:20:999]
14:30[:20.9]
4am
4 PM
[0]4[:30:20:500]AM
Nelle istruzioni seguenti vengono mostrati i valori restituiti dei diversi input della funzione CAST.
SELECT CAST('01/01/2000 14:30' AS datetime2)
--Returns: 2000-01-01 14:30:00.0000000
SELECT CAST('01/01/2000 14:30:20:999' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9990000
SELECT CAST('01/01/2000 14:30:20.9' AS datetime2)
-- Returns: 2000-01-01 14:30:20.9000000
SELECT CAST('01/01/2000 4am' AS datetime2)
-- Returns: 2000-01-01 04:00:00.0000000
SELECT CAST('01/01/2000 4 PM' AS datetime2)
-- Returns: 2000-01-01 16:00:00.0000000
SELECT CAST('01/01/2000 04:30:20:500AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
SELECT CAST('01/01/2000 04:30:20:500 AM' AS datetime2)
-- Returns: 2000-01-01 04:30:20.5000000
GO
È possibile specificare il suffisso AM o PM per indicare se il valore di ora precede o segue il mezzogiorno. La combinazione di maiuscole e minuscole di AM e PM non è rilevante.
L'ora può essere espressa nel formato a 12 o a 24 ore. I valori relativi all'ora vengono interpretati nel modo seguente:
Il valore relativo all'ora 00 rappresenta l'ora dopo mezzanotte (AM), indipendentemente dall'indicazione AM. Non è possibile specificare PM quando l'ora è uguale a 00.
I valori di ora compresi tra 01 e 11 rappresentano le ore antimeridiane (prima di mezzogiorno) se non si specifica né AM né PM o se si specifica AM, mentre rappresentano le ore postmeridiane (dopo mezzogiorno) se si specifica PM.
Il valore di ora 12 rappresenta l'ora che inizia a mezzogiorno se non si specifica né AM né PM. Rappresenta invece l'ora che inizia a mezzanotte se si specifica AM e l'ora che inizia a mezzogiorno se si specifica PM. Ad esempio, 12:01 indica un minuto dopo mezzogiorno, così come 12:01 PM, mentre 12:01 AM indica un minuto dopo la mezzanotte. 12:01 AM equivale a 00:01 o 00:01 AM.
I valori di ora compresi tra 13 e 23 rappresentano le ore postmeridiane se non si specifica AM o PM e le ore postmeridiane se si specifica PM. Non è possibile specificare AM per valori di ora compresi tra 13 e 23.
Il valore di ora 24 non è valido. Per indicare la mezzanotte, utilizzare 12:00 AM o 00:00.
È possibile far precedere i millisecondi dai due punti (:) o da un punto (.). Un valore preceduto da due punti indica i millesimi di secondo. Un valore preceduto da un punto indica i decimi di secondo se è composto da una sola cifra, i centesimi di secondo se è composto da due e i millesimi di secondo se è composto da tre cifre. Ad esempio, 12:30:20:1 indica che sono trascorsi ventuno millesimi di secondo dalle 12:30, mentre 12:30:20.1 indica che sono trascorsi venti secondi e un decimo dalle 12:30.
Formato data e ora ODBC
L'API ODBC definisce sequenze di escape per la rappresentazione dei valori di data e ora, che in ODBC sono denominati dati timestamp. Il formato timestamp di ODBC è anche supportato dalla definizione del linguaggio OLE DB (DBGUID-SQL) e dal provider OLE DB Microsoft per SQL Server. Nelle applicazioni che utilizzano API basate su ADO, OLE DB e ODBC è possibile utilizzare il formato timestamp ODBC per la rappresentazione di valori di data e ora.
In SQL Server i dati ODBC vengono sempre considerati tipi di dati datetime.
Il formato delle sequenze di escape del timestamp ODBC è il seguente:
{ literal_type 'constant_value' }
literal_type
Specifica il tipo di sequenza di escape. Di seguito sono riportati i valori di argomento validi per literal_type.d = solo data
t = solo ora
ts = timestamp (ora + data)
'constant_value'
Rappresenta il valore della sequenza di escape. constant_value deve seguire i formati seguenti per ogni valore literal_type:literal_type
formato constant_value
d
YYYY-MM-DD
t
hh:mm:ss[.fff]
ts
YYYY-MM-DDhh:mm:ss[.fff]
Di seguito sono riportati alcuni esempi di costanti per valori di data e ora ODBC:
{ ts '1998-05-02 01:23:56.123' }
{ d '1990-10-02' }
{ t '13:33:41' }
Occorre fare attenzione a non confondere il nome del tipo di dati timestamp OLE DB e ODBC con il nome del tipo di dati Transact-SQLtimestamp. Il tipo di dati timestamp OLE DB e ODBC registra valori di data e ora. Il tipo di dati timestampTransact-SQL è un tipo di dati binario che prevede valori non relativi all'ora.
Conversione data, ora, datetime2 e datetimeoffset
Esistono due tipi di conversioni tra tipi di dati diversi, quello esplicito e quello implicito. Le conversioni implicite si verificano senza utilizzare le funzioni CAST o CONVERT. Nelle conversioni esplicite sono necessarie le funzioni CAST e CONVERT.
Conversione tra stringhe letterali e time (n), data, datetime2 (n) e datetimeoffset (n)
Le conversioni da valori letterali stringa a tipi di data e ora sono consentite se tutte le parti delle stringhe hanno formati validi. In caso contrario, viene generato un errore di runtime.
Le conversioni implicite o esplicite che non specificano uno stile, dai tipi di data e ora ai valori letterali stringa, saranno nel formato predefinito della sessione corrente.
Per la conversione implicita dai tipi di dati di data, ora, datetime2 e datetimeoffset alle stringhe di caratteri, viene utilizzato il formato standard SQL YYY-MM-DD hh:mm:ss.[nnnnnnn], CONVERT stile 121. Il formato di stile CONVERT 0, dd yyyy hh:miAM (or PM) mese viene applicato ai tipi di dati datetime e smalldatetime.
Nella tabella seguente sono mostrate le regole per la conversione tra date, time, datetime2 e i tipi datetimeoffset e i valori letterali stringa.
Valore letterale stringa di input |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|
ODBC DATE |
Vedere la nota 1. |
Vedere la nota 1. |
Vedere la nota 1. |
Vedere la nota 1. |
ODBC TIME |
Vedere la nota 1. |
Vedere la nota 1. |
Vedere la nota 1. |
Vedere la nota 1. |
ODBC DATETIME |
Vedere la nota 1. |
Vedere la nota 1. |
Vedere la nota 1. |
Vedere la nota 1. |
solo DATE |
Semplice |
Vengono forniti i valori predefiniti. |
Il valore predefinito per la parte di TIME è 00:00:00. |
Il valore predefinito per la parte di TIME è 00:00:00. Il valore predefinito di TIMEZONE è +00:00:00. |
solo TIME |
Vengono forniti i valori predefiniti. |
Semplice |
Il valore predefinito per la parte di DATE è 1900-1-1. |
Il valore predefinito per la parte di DATE è 1900-1-1. Il valore predefinito di TIMEZONE è +00:00. |
solo TIMEZONE |
Vengono forniti i valori predefiniti. |
Vengono forniti i valori predefiniti. |
Vengono forniti i valori predefiniti. |
Vengono forniti i valori predefiniti. |
DATE + TIME |
Viene utilizzata la parte di DATE della stringa di input. |
Viene utilizzata la parte di TIME della stringa di input. |
Semplice |
Il valore predefinito di TIMEZONE è +00:00:00. |
DATE + TIMEZONE |
Non consentiti |
Non consentiti |
Non consentiti |
Non consentiti |
TIME + TIMEZONE |
Vengono forniti i valori predefiniti. |
Viene utilizzata la parte di TIME della stringa di input. |
Il valore predefinito per la parte di DATE è 1900-1-1. L'input TIMEZONE viene ignorato. |
Il valore predefinito per la parte di DATE è 1900-1-1. |
DATE + TIME + TIMEZONE |
Verrà utilizzata la parte DATE di DATETIME locale. |
Verrà utilizzata la parte TIME di DATETIME locale. |
Verrà utilizzato DATETIME locale. |
Semplice |
Note sulla conversione
Viene eseguito il mapping dei valori letterali stringa ODBC al tipo di dati datetime. Tutte le operazione di assegnazione dai valori letterali di ODBC DATETIME ai tipi date, time, datetime2 o datetimeoffse provocheranno una conversione implicita tra datetime e tali tipi come definito dalle regole di conversione.
Il valore della precisione frazionaria dei secondi di datetime ha un'accuratezza di un trecentesimo di secondo (equivalente a 3,33 millisecondi o 0,00333 secondi). I valori vengono arrotondati in base a incrementi di 0,000, 0,003 o 0,007 secondi. '08/22/1995 10:15:19:999' viene arrotondato perché '.999' supera la precisione.
Per time(3), datetime2(3) o datetimeoffset(3), la precisione frazionaria dei secondi ha un'accuratezza di un millisecondo. Pertanto, '1995-8-22 10:15:19:999' non sarà arrotondato.
La parte offset di input di TIMEZONE deve essere composta sempre da cifre doppie sia per hh sia per mm. Il segno, + o -, è obbligatorio.
Conversione tra tipi di dati di data e ora
Nelle tabelle di questa sezione vengono descritte le modalità di conversione dei seguenti tipi di dati date e time in tipi di dati date e time diversi:
date
time(n)
datetime
smalldatetime
datetimeoffset(n)
datetime2
Tipo di dati date
Nella tabella seguente viene descritto il risultato della conversione di un tipo di dati date in un altri tipi di dati date e time.
Tipo di dati in cui viene eseguita la conversione |
Dettagli relativi alla conversione |
|---|---|
time(n) |
La conversione non viene eseguita e viene generato il messaggio di errore 206: "Conflitto del tipo di operando: date è incompatibile con time". |
datetime |
La data viene copiata. Nel codice seguente vengono illustrati i risultati della conversione di un valore date in un valore datetime. |
smalldatetime |
Quando il valore date è compreso nell'intervallo di un tipo di dati smalldatetime, il componente relativo alla data viene copiato mentre quello relativo all'ora viene impostato su 00:00:00.000. Quando il valore date non è compreso nell'intervallo di un valore smalldatetime, viene generato il messaggio di errore 242: "La conversione di un tipo di dati date in un smalldatetime ha generato un valore non compreso nell'intervallo dei valori consentiti" e il valore smalldatetime viene impostato su NULL. Nel codice seguente vengono illustrati i risultati della conversione di un valore date in un valore smalldatetime. |
datetimeoffset(n) |
La data viene copiata e l'ora viene impostata su 00:00.0000000 +00:00. Nel codice seguente vengono illustrati i risultati della conversione di un valore date in un valore datetimeoffset(3). |
datetime2(n) |
Il componente relativo alla data viene copiato e quello relativo all'ora viene impostato su 00:00.000000. Nel codice seguente vengono illustrati i risultati della conversione di un valore date in un valore datetime2(3). |
Tipi di dati time (n)
Nella tabella seguente viene descritto il risultato della conversione di un tipo di dati time in un altri tipi di dati date e time.
Tipo di dati in cui viene eseguita la conversione |
Dettagli relativi alla conversione |
|---|---|
time(n) |
L'ora, il minuto e i secondi vengono copiati. Quando la precisione della destinazione è minore di quella dell'origine, i secondi frazionari verranno troncati per rispettare la precisione della destinazione. Nell'esempio seguente vengono illustrati i risultati della conversione di un valore time(4) in un valore time(3). |
date |
La conversione non viene eseguita e viene generato il messaggio di errore 206: "Conflitto del tipo di operando: date è incompatibile con time". |
datetime |
I valori dell'ora, dei minuti e dei secondi vengono copiati e il componente relativo alla data viene impostato su '1900-01-01'. Quando la precisione dei secondi frazionari del valore time(n) è maggiore di tre cifre, il risultato di datetime verrà troncato. Nel codice seguente vengono illustrati i risultati della conversione di un valore time(4) in un valore datetime. |
smalldatetime |
I valori dell'ora e dei minuti vengono copiati e il componente relativo alla data viene impostato su '1900-01-01', mentre secondi e i secondi frazionari vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore time(4) in un valore smalldatetime. |
datetimeoffset(n) |
L'ora viene copiata e la data viene impostata su '1900-01-01'. La differenza di fuso orario è impostata su +00:00. Se la precisione dei secondi frazionari del valore time(n) è maggiore di quella del valore datetimeoffset(n), la prima precisione verrà troncata per rispettare la seconda. Nell'esempio seguente vengono illustrati i risultati della conversione di un valore time(4) in un tipo datetimeoffset(3). |
datetime2(n) |
La data viene impostata su '1900-01-01', il componente relativo all'ora viene copiato e la differenza di fuso orario viene impostata su 00:00. Se la precisione dei secondi frazionari del valore datetime2(n) è maggiore di quella del valore time(n) , la prima precisione verrà troncata per rispettare la seconda. Nell'esempio seguente vengono illustrati i risultati della conversione di un valore time(4) in un valore datetime2(2). |
Tipi di dati datetime
Nella tabella seguente viene descritto il risultato della conversione di un tipo di dati datetime in un altri tipi di dati date e time.
Tipo di dati in cui viene eseguita la conversione |
Dettagli relativi alla conversione |
|---|---|
date |
Il giorno, il mese e l'anno vengono copiati. Il componente relativo all'ora viene impostato su 00:00:00.000. Nel codice seguente vengono illustrati i risultati della conversione di un valore date in un valore datetime. |
time(n) |
Il componente relativo all'ora viene copiato e quello relativo alla data viene impostato su '1900-01-01'. Quando la precisione frazionaria del valore time(n) è maggiore di tre cifre, il valore verrà troncato. Nell'esempio seguente vengono illustrati i risultati della conversione di un valore time(4) in un valore datetime. |
smalldatetime |
Le ore e i minuti vengono copiati, mentre i secondi e i secondi frazionari vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore smalldatetime in un valore datetime. |
datetimeoffset(n) |
I componenti relativi alla data e all'ora vengono copiati. Il fuso orario viene troncato. Quando la precisione frazionaria del valore datetimeoffset(n) è maggiore di tre cifre, il valore verrà troncato. Nell'esempio seguente vengono illustrati i risultati della conversione di un valore datetimeoffset(4) in un valore datetime. |
datetime2(n) |
La data e l'ora vengono copiate. Quando la precisione frazionaria del valore datetime2(n) è maggiore di tre cifre, il valore verrà troncato. Nell'esempio seguente vengono illustrati i risultati della conversione di un valore datetime2(4) in un valore datetime. |
Tipo di dati smalldatetime
Nella tabella seguente viene descritto il risultato della conversione di un tipo di dati smalldatetime in un altri tipi di dati date e time.
Tipo di dati in cui viene eseguita la conversione |
Dettagli relativi alla conversione |
|---|---|
date |
Il giorno, il mese e l'anno vengono copiati. Nel codice seguente vengono illustrati i risultati della conversione di un valore smalldatetime in un valore date. |
time(n) |
Le ora, i minuti e i secondi vengono copiati, mentre i secondi frazionari vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore smalldatetime in un valore time(4). |
datetime |
Il valore smalldatetime viene copiato nel valore datetime, I secondi frazionari vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore smalldatetime in un valore datetime. |
datetimeoffset(n) |
Il valore smalldatetime viene copiato nel valore datetimeoffset(n). I secondi frazionari vengono impostati su 0, mentre la differenza di fuso orario viene impostata su +00:0. Nel codice seguente vengono illustrati i risultati della conversione di un valore smalldatetime in un valore datetimeoffset(4). |
datetime2(n) |
Il valore smalldatetime viene copiato nel valore datetime2(n). I secondi frazionari vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore smalldatetime in un valore datetime2(4). |
Tipo di dati datetimeoffset(n)
Nella tabella seguente viene descritto il risultato della conversione di un tipo di dati datetimeoffset(n) in un altri tipi di dati date e time.
Tipo di dati in cui viene eseguita la conversione |
Dettagli relativi alla conversione |
|---|---|
date |
Il giorno, il mese e l'anno vengono copiati. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetimeoffset(4) in un valore date. |
time(n) |
L'ora, il minuto, il secondo e i secondi vengono copiati. Il valore del fuso orario viene troncato. Se la precisione del valore datetimeoffset(n) è maggiore di quella del valore time(n), il primo valore viene troncato. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetimeoffset(4) in un valore time(3). |
datetime |
Il valori della data e dell'ora vengono copiati, mentre fuso orario viene troncato. Quando la precisione frazionaria del valore datetimeoffset(n) è maggiore di tre cifre, il valore viene troncato. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetimeoffset(4) in un valore datetime. |
smalldatetime |
La data, le ore e i minuti vengono copiati, mentre i secondi vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetimeoffset(3) in un valore smalldatetime. |
datetime2(n) |
La data e l'ora vengono copiate nel valore datetime2, mentre il fuso orario viene troncato. Se la precisione del valore datetime2(n) è maggiore di quella del valore datetimeoffset(n), i secondi frazionari vengono troncati. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetimeoffset(4) in un valore datetime2(3). |
Tipi di dati datetime2
Nella tabella seguente viene descritto il risultato della conversione di un tipo di dati datetime2 in un altri tipi di dati date e time.
Tipo di dati in cui viene eseguita la conversione |
Dettagli relativi alla conversione |
|---|---|
date |
Il giorno, il mese e l'anno vengono copiati. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetime2(4) in un valore date. |
time(n) |
L'ora, il minuto, il secondo e i secondi vengono copiati. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetime2(4) in un valore time(3). |
datetime |
I valori della data e dell'ora vengono copiati. Quando la precisione frazionaria del valore datetimeoffset(n) è maggiore di tre cifre, il valore viene troncato. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetime2 in un valore datetime. |
smalldatetime |
La data, le ore e i minuti vengono copiati, mentre i secondi vengono impostati su 0. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetime2 in un valore smalldatetime. |
datetimeoffset(n) |
Il valore datetime2(n) viene copiato nel valore datetimeoffset(n). La differenza di fuso orario viene impostata su +00:0. Se la precisione del valore datetime2(n) è maggiore di quella del valore datetimeoffset(n), il primo valore viene troncato. Nel codice seguente vengono illustrati i risultati della conversione di un valore datetime2(5) in un valore datetimeoffset(3). |
Utilizzo di CAST e CONVERT con time, date, datetime2 e datetimeoffset
In questa sezione vengono fornite le informazioni sulla conversione tra tipi di dati relativi a data e ora.
Conversione in datetimeoffset
- Ogni volta che il valore datetimeoffset con fuso orario deve essere derivato implicitamente da un valore senza un fuso orario (ad esempio, in una semplice operazione di assegnazione), il valore senza fuso orario viene trattato come locale e il dislocamento del fuso orario (00:00) predefinito corrente viene sottratto per ottenere il valore UTC.
- Il fuso orario UTC (00:00) viene aggiunto sempre quando si converte il tipo di dati senza fuso orario in datetimeoffset per le conversioni seguenti:
datedate in datetimeoffsetdatetimeoffset
timetime in datetimeoffsetdatetimeoffset
datetime2datetime2 in datetimeoffsetdatetimeoffset
datetime o smalldatetime in datetimeoffset
Valori letterali stringa in date valido, time o datetime senza fuso orario in datetimeoffset
Conversione da datetimeoffset
Quando si converte da datetimeoffset al seguente tipo di dati senza fuso orario, lo stile 0 (impostazione predefinita) indica sempre che il valore restituito date, timedatetime2, datetime o smalltime è nel formato locale della differenza di fuso orario conservata. Lo stile 1 indica sempre il formato UTC.
Ogni volta che una data o un valore senza fuso orario viene derivato implicitamente in una delle conversioni seguenti, il valore datetimeoffset viene trattato come UTC. Il dislocamento del fuso orario conservato viene aggiunto al valore per ottenere l'ora locale. Il risultato, senza la differenza del fuso orario, è in ora locale.
datetimeoffsetdatetimeoffset in datedate
datetimeoffsetdatetimeoffset in timetime
datetimeoffsetdatetimeoffset in datetime2datetime2
datetimeoffset in datetime o smalldatetime
Impossibile utilizzare gli stili 0 e 1 per la conversione di datetimeoffset in stringhe. Invece, eseguire prima la conversione da datetimeoffset a datetime2 o datetime e quindi a varchar o char.
Se in uno stile CONVERT esistente è inclusa la parte relativa all'ora e la conversione è da datetimeoffset a una stringa, la differenza di fuso orario (ad eccezione dello stile 127) viene inclusa. Se non si desidera la differenza di fuso orario, è possibile utilizzare CAST in datetime2 e successivamente nella stringa.
Tutti gli stili della data e dell'ora esistenti verranno applicati a qualsiasi conversione di datetimeoffset in stringhe e la differenza di fuso orario verrà mantenuta.
La parte relativa alla differenza di fuso orario della stringa di input deve essere sempre composta da cifre doppie sia per hh sia per mm. Il segno, + o -, è obbligatorio.
Informazioni rimosse nella conversione
Quando si esegue la conversione da datetime2 o datetimeoffset a date, non viene effettuato alcun arrotondamento e la parte relativa alla data viene estratta in modo esplicito. Per datetimeoffset, l'estrazione viene eseguita per la data e l'ora locali ma non per il valore UTC.
Per qualsiasi conversione implicita da datetimeoffset a date, time, datetime2, datetime o smalldatetime, la conversione si basa sul valore della data e dell'ora locali (per la differenza di fuso orario persistente). Ad esempio, quando il valore datetimeoffset(3) 2006-10-21 12:20:20.999 -8:00 viene convertito in time(3), il risultato è 12:20:20.999 non 20:20:20.999(UTC).
Troncamento nella conversione
- Sono consentite le conversioni dei valori relativi all'ora con una precisione più elevata in valori con una precisione inferiore. I valori relativi a una precisione più elevata verranno troncati per rispettare il tipo con precisione inferiore.
Conversione dei secondi frazionari
Se uno stile include il formato dell'ora hh:mm:ss.mmm, il formato diventerà hh:mm:ss. [nnnnnnn] per time(n), datetime2(n) e datetimeoffset(n). Il numero di cifre dipende dalla specifica del tipo. Se si vuole solo una precisione di un millisecondo, è necessario prima eseguire la conversione in datetime2(3) e successivamente in stringa.
Per gli stili 9, 109, 13, 113, 21, 121, 130 e 131, il separatore due punti (:) che precede i secondi frazionari non viene supportato per i tipi time, datetime2 e datetimeoffset per la conversione in stringhe. Il formato della stringa di output con alcuni di questi stili sarà trasformato in un punto (.).
Argomenti di stile per le funzioni CONVERT
Nella tabella seguente sono elencati esempi di valori date, time, datetime2 e datetimeoffset relativi agli argomenti dello stile per la funzione CONVERT. Per ulteriori informazioni sullo stile, vedere la sezione degli argomenti di CAST e CONVERT (Transact-SQL)
Stile |
Associated standard |
Input/Output (3) format |
date |
time(n) |
datetime2(n) |
datetimeoffset(n) |
|---|---|---|---|---|---|---|
0 o 100 (1,2) |
Valore predefinito |
mes dd yyyy hh:miAM (o PM) |
Gen 1 2001 |
12:20PM |
Gen 1 2001 12:20PM |
Gen 1 2001 12:20PM -08:00 |
101 |
U.S. |
mm/dd/yyyy |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
102 |
ANSI |
yy.mm.dd |
2001.01.01 |
- |
2001.01.01 |
2001.01.01 |
103 |
Inglese/Francese |
dd/mm/yy |
01/01/2001 |
- |
01/01/2001 |
01/01/2001 |
104 |
Tedesco |
dd.mm.yy |
01.01.2001 |
- |
01.01.2001 |
01.01.2001 |
105 |
Italiano |
dd-mm-yy |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
106(1) |
- |
dd mes yy |
01 gen 2001 |
- |
01 gen 2001 |
01 gen 2001 |
107(1) |
- |
Mes dd, yy |
Gen 01, 2001 |
- |
Gen 01, 2001 |
Gen 01, 2001 |
108 |
- |
hh:mi:ss |
- |
12:20:20 |
12:20:20 |
12:20:20 |
9 o 109 (1,2) |
Valore predefinito + millisecondi |
mes dd yyyy hh:mi:ss:mmmAM (o PM) |
Gen 1 2001 |
12:20:20.1234567 AM |
Gen 1 2001 12:20:20.1234567PM |
Gen 1 2001 12:20:20:1230000PM -08:00 |
110 |
Stati Uniti |
mm-dd-yy |
01-01-2001 |
- |
01-01-2001 |
01-01-2001 |
111 |
Giappone |
yy/mm/dd |
2001/01/01 |
- |
2001/01/01 |
2001/01/01 |
112 |
ISO |
yymmdd |
20010101 |
- |
20010101 |
20010101 |
13 o 113 (1,2) |
Valore predefinito Europa + millisecondi |
dd mes yyyy hh:mi:ss:mmm(24h) |
01 gen 2001 |
12:20:20.1234567 |
01 gen 2001 12:20:20.1234567 |
01 gen 2001 12:20:20:1230000 -08:00 |
114 |
- |
hh:mi:ss:mmm(24h) |
- |
12:20:20.1234567 |
12:20:20.1234567 |
12:20:20:1230000 -08:00 |
20 o 120 (2) |
ODBC canonico |
yyyy-mm-dd hh:mi:ss(24h) |
2001-01-01 |
12:20:20 |
2001-01-01 12:20:20 |
2001-01-01 12:20:20 -08:00 |
21 o 121 (2) |
ODBC canonico (con millisecondi) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01 12:20:20.1234567 |
2001-01-01 12:20:20.1230000 -08:00 |
126 (4) |
ISO8601 |
yyyy-mm-ddThh:mi:ss.mmm (senza spazi) |
2001-01-01 |
12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
2001-01-01T 12:20:20.1234567 |
127(6, 7) |
ISO8601 con fuso orario Z. |
yyyy-mm-ddThh:mi:ss.mmmZ (senza spazi) |
2001-01-01 |
12:20:20.1234567Z |
2001-01-01T 12:20:20.1234567Z |
2001-01-01T20:20:20.1230000Z |
130 (1,2) |
Hijri (5) |
dd mes yyyy hh:mi:ss:mmmAM |
01 gen 2001 |
12:20:20.1230000PM |
01 Gen 2001 12:20:20.1230000PM |
1 Gen 2001 12:20:20:1230000PM -08:00 |
131 (2) |
Hijri (5) |
dd/mm/yy hh:mi:ss:mmmAM |
01/01/2001 |
12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM |
01/01/2001 12:20:20.1230000PM -08:00 |
1 Questi valori di stile restituiscono risultati non deterministici. Include tutti gli stili (yy) (senza secolo) e un subset di stili (yyyy) (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 (yyyy).
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 viene utilizzato l'algoritmo kuwaitiano.
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 Serverdatetime senza fuso orario. Z è l'indicatore per UTC-0 del fuso orario.
Limitazioni relative a data e ora di SQL Server
Nell'elenco seguente, la data e l'ora si riferiscono a qualsiasi tipo di dati data-ora che include una data o una parte dell'ora.
Nessun supporto dell'ora legale (DST) sul lato server per datetimeoffset.
Nessun supporto del calendario giuliano per la data.
Nessun supporto della rappresentazione dell'ora '24' per mezzanotte.
Nessun supporto di 'secondo di salto' oltre '59'.
Nessun supporto dell'ora di 'un nanosecondo' o maggiore per la precisione dei secondi frazionari.
Nessun supporto del fuso orario per l'ora.
Nessun supporto dell'operazione INTERVAL standard SQL.
Compatibilità con le versioni precedenti dei client legacy
Alcuni client legacy non supportano i tipi di dati time, date, datetime2 e datetimeoffset aggiunti in SQL Server 2008. Nella tabella seguente viene illustrato il mapping del tipo tra un'istanza di livello principale di SQL Server 2008 e i client legacy.
Tipo di dati di SQL Server 2008 |
Formato predefiniti dei valori letterali stringa passati al client legacy |
ODBC delle versioni precedenti |
OLEDB delle versioni precedenti |
JDBC delle versioni precedenti |
SQLCLIENT delle versioni precedenti |
|---|---|---|---|---|---|
time |
hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR o SQL_VARCHAR |
DBTYPE_WSTR o DBTYPE_STR |
Java.sql.String |
Stringa o SqString |
date |
YYYY-MM-DD |
SQL_WVARCHAR o SQL_VARCHAR |
DBTYPE_WSTR o DBTYPE_STR |
Java.sql.String |
Stringa o SqString |
datetime2 |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] |
SQL_WVARCHAR o SQL_VARCHAR |
DBTYPE_WSTR o DBTYPE_STR |
Java.sql.String |
Stringa o SqString |
datetimeoffset |
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm |
SQL_WVARCHAR o SQL_VARCHAR |
DBTYPE_WSTR o DBTYPE_STR |
Java.sql.String |
Stringa o SqString |
Esempi
A. Confronto tra i tipi di dati di data e ora
Nell'esempio seguente vengono confrontati i risultati dell'esecuzione del cast di un valore letterale stringa per ogni tipo di dati di data e ora. Quando si tenta di eseguire il CAST di un valore letterale stringa con una precisione frazionaria dei secondi che supera quella consentita per smalldatetime o datetime, viene generato un errore.
SELECT
CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date'
,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS
'smalldatetime'
,CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime'
,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS
'datetime2'
,CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS
'datetimeoffset';
Tipo di dati |
Output |
|---|---|
Time |
12:35:29.1234567 |
Date |
2007-05-08 |
Smalldatetime |
2007-05-08 12:35:00 |
Datetime |
2007-05-08 12:35:29.123 |
datetime2(7) |
2007-05-08 12:35:29.1234567 |
datetimeoffset(7) |
2007-05-08 12:35:29.1234567 +12:15 |
B. Recupero della data e dell'ora correnti di sistema
Nell'esempio seguente viene illustrato come utilizzare le funzioni di sistema di SQL Server che restituiscono la data e l'ora correnti.
SELECT SYSDATETIME() AS 'SYSDATETIME'
--Results
--SYSDATETIME
--2007-10-22 14:10:41.7984554
--(1 row(s) affected)
SELECT SYSDATETIMEOFFSET() AS 'SYSDATETIMEOFFSET'
--Results
--SYSDATETIMEOFFSET
--2007-10-22 14:11:34.2607172 -0
--(1 row(s) affected)
SELECT SYSUTCDATETIME() AS 'SYSUTCDATETIME'
--Results
--SYSUTCDATETIME
--2007-10-22 21:12:11.7069470
--(1 row(s) affected)
SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
--Results
--CURRENT_TIMESTAMP
-------------------------
--2007-10-22 14:12:33.320
--(1 row(s) affected)
SELECT GETDATE() AS 'GETDATE'
--Results
--GETDATE
--2007-10-22 14:13:57.943
--(1 row(s) affected)
SELECT GETUTCDATE() AS 'GETUTCDATE'
--Results
--GETUTCDATE
--2007-10-22 21:14:35.657
--(1 row(s) affected)
C. Ricerca di tutti i valori datetime2 di un giorno
- Nell'esempio seguente viene illustrato come ricercare tutti i valori della data e dell'ora in un giorno.
-- Create a table that contains with the following dates:
-- The last date-time in 2005-04-06, '2005-04-06 23:59:59.9999999'
-- The first date-time in 2005-04-07, '2005-04-07 00:00:00.0000000'
-- The last date-time in 2005-04-07, '2005-04-07 23:59:59.9999999'
-- The first date-time in 2005-04-08, '2005-04-08 00:00:00.0000000'
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 00:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-07 23:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-08 00:00:00.0000000');
-- The following four SELECT statements show different ways to find
-- only the two rows that contain 2005-04-07 dates.
--Use CONVERT.
SELECT MyDate
FROM #Search
WHERE CONVERT(date,MyDate) = '2005-04-07';
--Use >= and <=.
SELECT MyDate
FROM #Search
WHERE MyDate >= '2005-04-07 00:00:00.0000000'
AND MyDate <= '2005-04-07 23:59:59.9999999';
--Use > and <.
SELECT MyDate
FROM #Search
WHERE MyDate > '2005-04-06 23:59:59.9999999'
AND MyDate < '2005-04-08 00:00:00.0000000';
--Use BETWEEN AND.
SELECT MyDate
FROM #Search
WHERE MyDate BETWEEN '2005-04-07 00:00:00.0000000'
AND '2005-04-07 23:59:59.9999999';
DROP TABLE #Search
GO
D. Ricerca di un periodo di tempo in un giorno
Negli esempi seguenti vengono illustrate le diverse modalità di ricerca delle date con valori dell'ora per individuare un intervallo di tempo.
-- Create a table called Search and insert
-- five different time values for the same
-- date.
CREATE TABLE #Search
(
MyDate datetime2
);
INSERT INTO #Search(MyDate)VALUES('2005-04-06 08:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.0000000');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 09:00:00.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 16:59:59.9999999');
INSERT INTO #Search(MyDate)VALUES('2005-04-06 17:00:00.0000000');
-- The following SELECT statements show different ways
-- to search for dates that have time values to find a
-- time range.
--Using CONVERT with time (0) to ignore fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(0),MyDate) = '09:00:00';
--Returns two rows (ignores fractional seconds):
--2005-04-06 08:59:59.9999999
--2005-04-06 09:00:00.0000000
--Using CONVERT with time (7) to include fractional seconds
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) = '09:00:00';
--Returns one row (matches fractional seconds):
--2005-04-06 09:00:00.0000000
--Each of the SELECT statements below use CONVERT
--to find all times in an eight-hour period.
--Use CONVERT with >= and <=.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) >= '09:00:00.0000000'
AND CONVERT(time(7),MyDate) <= '16:59:59.9999999'
--Use CONVERT with > and <.
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) > '08:59:59.9999999'
AND CONVERT(time(7),MyDate) < '17:00:00.0000000';
--Use CONVERT with BETWEEN AND
SELECT MyDate
FROM #Search
WHERE CONVERT(time(7),MyDate) BETWEEN '09:00:00.0000000'
AND '16:59:59.9999999';
DROP TABLE #Search
GO
E. Utilizzo di DATEPART e DATEADD per individuare il primo e l'ultimo giorno di una data specificata da datepart
Nell'esempio seguente viene illustrato come restituire il primo o l'ultimo giorno di un valore datepart.
-- When several months, quarters, or years are added to the base
-- year,1900-01-01, the returned day is the first day of a month.
-- To calculate the last day of the current month, you need to
--
-- 1. Find the difference in months between today and the base
-- year (0 ='1900-01-01'). This is accomplished by
--
-- DATEDIFF(month, 0, SYSDATETIME())
-- 2. Add that number of months plus 1 to the base year (0)
-- to obtain the first day of the next month.
-- DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0)
-- 3. Subtract 1 day.
--
--Find the first day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()), 0);
--Find the last day of the current month.
SELECT DATEADD(month, DATEDIFF(month, 0, SYSDATETIME()) + 1, 0) - 1;
-- Find the first day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, 0, SYSDATETIME()), 0);
-- Find the last day of the current quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, SYSDATETIME()), -1);
-- Find the first day of the current year.
SELECT DATEADD(year, DATEDIFF(year, 0, SYSDATETIME()), 0);
-- Find the last day of the current year.
SELECT DATEADD(year, DATEDIFF(year, -1, SYSDATETIME()), -1);
F. Utilizzo di argomenti datepart definiti dall'utente per DATEADD, DATEDIFF, DATENAME e DATEPART
Nell'esempio seguente è creata una funzione scalare definita dall'utente che aggiunge una costante a qualsiasi parte di un valore datetime2.
USE tempdb
GO
CREATE FUNCTION UserDateadd
(
@datepart nvarchar(11)
,@number int
,@date datetime2
)
RETURNS datetime2
AS
BEGIN
DECLARE @Return datetime2
SELECT @Return = CASE @datepart
WHEN 'year' THEN DATEADD(year,@number,@date)
WHEN 'yy' THEN DATEADD(year,@number,@date)
WHEN 'yyyy' THEN DATEADD(year,@number,@date)
WHEN 'quarter' THEN DATEADD(quarter,@number,@date)
WHEN 'qq' THEN DATEADD(quarter,@number,@date)
WHEN 'month' THEN DATEADD(month,@number,@date)
WHEN 'mm' THEN DATEADD(month,@number,@date)
WHEN 'm' THEN DATEADD(month,@number,@date)
WHEN 'dayofyear' THEN DATEADD(dayofyear,@number,@date)
WHEN 'dy' THEN DATEADD(dayofyear,@number,@date)
WHEN 'y' THEN DATEADD(dayofyear,@number,@date)
WHEN 'day' THEN DATEADD(day,@number,@date)
WHEN 'dd' THEN DATEADD(day,@number,@date)
WHEN 'd' THEN DATEADD(day,@number,@date)
WHEN 'week' THEN DATEADD(week,@number,@date)
WHEN 'wk' THEN DATEADD(week,@number,@date)
WHEN 'ww' THEN DATEADD(week,@number,@date)
WHEN 'weekday' THEN DATEADD(weekday,@number,@date)
WHEN 'wk' THEN DATEADD(weekday,@number,@date)
WHEN 'w' THEN DATEADD(weekday,@number,@date)
WHEN 'hour' THEN DATEADD(hour,@number,@date)
WHEN 'hh' THEN DATEADD(hour,@number,@date)
WHEN 'minute' THEN DATEADD(minute,@number,@date)
WHEN 'mi' THEN DATEADD(minute,@number,@date)
WHEN 'n' THEN DATEADD(minute,@number,@date)
WHEN 'second' THEN DATEADD(second,@number,@date)
WHEN 'ss' THEN DATEADD(second,@number,@date)
WHEN 's' THEN DATEADD(second,@number,@date)
WHEN 'millisecond' THEN DATEADD(millisecond,@number,@date)
WHEN 'ms' THEN DATEADD(millisecond,@number,@date)
WHEN 'microsecond' THEN DATEADD(microsecond,@number,@date)
WHEN 'mcs' THEN DATEADD(microsecond,@number,@date)
WHEN 'nanosecond' THEN DATEADD(nanosecond,@number,@date)
WHEN 'ns' THEN DATEADD(nanosecond,@number,@date)
END
return @Return
END
GO
SELECT GetDate()
SELECT dbo.UserDateadd('year', 1, GetDate())
GO
G. Utilizzo di DATEPART per eseguire il raggruppamento in base alle parti di una data
Nell'esempio seguente viene utilizzato il database di esempio AdventureWorks. DATEPART viene utilizzato per raggruppare il totale delle vendite in base al giorno della settimana, al mese, all'anno e in base alla combinazione anno/mese/giorno della settimana.
USE AdventureWorks
GO
SELECT SUM(TotalDue) AS 'Total Sales'
,DATEPART(year,OrderDate)AS 'By Year'
,DATEPART(month,OrderDate) AS 'By Month'
,DATEPART(weekday,OrderDate) AS 'By Weekday'
FROM Sales.SalesOrderHeader
WHERE YEAR(OrderDate) IN('2003','2004')
GROUP BY GROUPING SETS
(
(DATEPART(year,OrderDate))
,(DATEPART(month,OrderDate))
,(DATEPART(weekday,OrderDate))
,(
DATEPART(year,OrderDate)
,DATEPART(month,OrderDate),
DATEPART(weekday,OrderDate))
)
ORDER BY DATEPART(year,OrderDate)
,DATEPART(month,OrderDate)
,DATEPART(weekday,OrderDate)
Vedere anche