datetimeoffset (Transact-SQL)

Articolo
11/19/2024

Si applica a: SQL Server database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL di Azure Synapse Analytics Platform System (PDW) in Microsoft Fabric Warehouse nel database SQL di Microsoft Fabric in Microsoft Fabric

Definisce una data combinata con un'ora di un giorno basata su un orologio di 24 ore, ad esempio datetime2, e aggiunge la consapevolezza del fuso orario in base all'ora UTC (Coordinated Universal Time).

Descrizione di datetimeoffset

Proprietà	valore
Sintassi	*DATETIMEOFFSET [ ( precisione* dei secondi frazionari ) ]**
Utilizzo	`DECLARE @MyDatetimeoffset DATETIMEOFFSET(7);` `CREATE TABLE Table1 (Column1 DATETIMEOFFSET(7));`
Formati di valori letterali stringa predefiniti (usati per il client di livello inferiore)	Aaaa-MM-gg HH:mm:ss[.nnnnnnn] [{+\|-}hh:mm] Per altre informazioni, vedere la sezione Compatibilità con le versioni precedenti per i client di livello inferiore che segue.
Intervallo di date	`0001-01-01` attraverso `9999-12-31` Dal 1 gennaio 1 al 31 dicembre 9999
Intervallo di tempo	`00:00:00` attraverso `23:59:59.9999999`
Intervallo di offset del fuso orario	`-14:00` attraverso `+14:00`
Intervalli di elementi	`yyyy` è costituito da quattro cifre, che vanno da `0001` a `9999`, che rappresentano un anno. `MM` è costituito da due cifre, comprese tra `01` e `12`, che rappresentano un mese nell'anno specificato. `dd` è costituito da due cifre, che vanno da `01` a in base al `31` mese, che rappresentano un giorno del mese specificato. `HH` è di due cifre, compreso tra `00` e `23`, che rappresentano l'ora. `mm` è costituito da due cifre, comprese tra `00` e `59`, che rappresentano il minuto. `ss` è di due cifre, che vanno da `00` a `59`, che rappresentano il secondo. n è da zero a sette cifre, compreso tra `0` e `9999999`, che rappresentano i secondi frazionari. `hh` è costituito da due cifre comprese tra `-14` e `+14`. `mm` è costituito da due cifre comprese tra `00` e `59`.
Lunghezza carattere	26 posizioni minime (a-MM-gg HH:mm:ss {+\|-}hh:mm) a 34 massime (aaaa-MM-gg HH:mm:ss.nnnnnnn {+\|-}hh:mm)
Precisione, scala	Vedi la tabella seguente.
Dimensioni archiviazione	10 byte, fisso è l'impostazione predefinita con precisione frazionaria di 100-ns.
Precisione	100 nanosecondi
Valore predefinito	`1900-01-01 00:00:00 00:00`
Calendario	Gregoriano
Precisione frazionaria definita dall'utente	Sì
Riconoscimento e conservazione della differenza di fuso orario	Sì
Riconoscimento dell'ora legale	No

Scala specificata	Risultato (precisione, scala)	Lunghezza della colonna (byte)	Precisione in secondi frazionari
datetimeoffset	(34, 7)	10	7
datetimeoffset(0)	(26, 0)	8	da 0 a 2
datetimeoffset(1)	(28, 1)	8	da 0 a 2
datetimeoffset(2)	(29, 2)	8	da 0 a 2
datetimeoffset(3)	(30, 3)	9	Da 3 a 4
datetimeoffset(4)	(31, 4)	9	Da 3 a 4
datetimeoffset(5)	(32, 5)	10	Da 5 a 7
datetimeoffset(6)	(33, 6)	10	Da 5 a 7
datetimeoffset(7)	(34, 7)	10	Da 5 a 7

Formati di valore letterale stringa supportati per datetimeoffset

Nella tabella seguente vengono elencati i formati di valore letterale stringa ISO 8601 supportati per datetimeoffset. Per informazioni sui formati alfabetici, numerici, non temporaneamente e ora per le parti di data e ora di datetimeoffset, vedere data (Transact-SQL) e ora (Transact-SQL).

ISO 8601	Descrizione
aaaa-MM-ddTHH:mm:ss[.nnnnnnn][{+\|-}hh:mm]	Questi due formati non sono interessati dalle impostazioni locali della `SET LANGUAGE` sessione e `SET DATEFORMAT` . Gli spazi non sono consentiti tra datetimeoffset e le parti datetime.
aaaa-MM-ddTHH:mm:ss[.nnnnnnn]Z (UTC)	In base alla definizione ISO questo formato indica che la parte datetime deve essere espressa in formato UTC (Coordinated Universal Time). Ad esempio, `1999-12-12 12:30:30.12345 -07:00` deve essere rappresentato come `1999-12-12 19:30:30.12345Z`.

Nell'esempio seguente vengono confrontati i risultati dell'esecuzione del cast di una stringa ai tipi di dati date e time.

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',
    CAST('2007-05-08 12:35:29.1234567+12:15' AS DATETIMEOFFSET(7)) AS 'datetimeoffset IS08601';

Il set di risultati è il seguente.

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`	`2007-05-08 12:35:29.1234567`
`datetimeoffset`	`2007-05-08 12:35:29.1234567 +12:15`
`datetimeoffset IS08601`	`2007-05-08 12:35:29.1234567 +12:15`

Differenza di fuso orario

Una differenza di fuso orario specifica la differenza di fuso orario rispetto all'ora UTC per un valore time o datetime. La differenza di fuso orario può essere rappresentata nel formato [+|-] hh:mm:

hh è costituito da due cifre che vanno da 00 a 14 e rappresentano il numero di ore nella differenza di fuso orario.
mm è costituito da due cifre, comprese tra 00 e 59, che rappresentano il numero di minuti aggiuntivi nell'offset del fuso orario.
+ (più) o - (meno) è il segno obbligatorio per una differenza di fuso orario. Questo segno indica se l'offset del fuso orario viene aggiunto o sottratto dall'ora UTC per ottenere l'ora locale. L'intervallo valido di differenza di fuso orario è compreso tra -14:00 e +14:00.

L'intervallo di offset del fuso orario segue lo standard XML W3C per la definizione dello schema XSD ed è leggermente diverso dalla definizione standard di SQL 2003 a 12:59 +14:00.

Il parametro facoltativo di tipo precisione in secondi frazionari specifica il numero di cifre per la parte relativa ai secondi frazionari. Questo valore può essere un numero intero con un numero di cifre compreso tra 0 e 7 (100 nanosecondi). La precisione predefinita dei secondi frazionari è 100 ns (sette cifre per la parte frazionaria dei secondi).

I dati sono archiviati nel database ed elaborati, confrontati, ordinati e indicizzati nel server come in UTC. L'offset del fuso orario viene mantenuto nel database per il recupero.

Si presuppone che la differenza di fuso orario specificata sia sensibile all'ora legale (DST) e regolata per qualsiasi data /ora specificata nel periodo DST.

Per il tipo datetimeoffset , sia UTC che locale (per la differenza di fuso orario persistente o convertita) il valore datetime viene convalidato durante operazioni di inserimento, aggiornamento, aritmetico, conversione o assegnazione. Il rilevamento di un valore UTC o locale non valido (per la differenza di fuso orario persistente o convertito) genera un errore di valore non valido. Ad esempio, 9999-12-31 10:10:00 è valido in formato UTC, ma l'overflow nell'ora locale passa all'offset +13:50del fuso orario.

Sintassi di conversione del fuso orario

SQL Server 2016 (13.x) ha introdotto la AT TIME ZONE sintassi per facilitare le conversioni di fuso orario universale e con riconoscimento dell'ora legale. Questa sintassi è particolarmente utile quando si converte i dati senza offset di fuso orario in dati con offset di fuso orario. Per eseguire la conversione in un valore datetimeoffset corrispondente in un fuso orario di destinazione, vedere AT TIME ZONE.

Conformità agli standard ANSI e ISO 8601

Le sezioni di conformità ANSI e ISO 8601 degli articoli relativi a data e ora si applicano a datetimeoffset.

Compatibilità con le versioni precedenti dei client

Alcune versioni precedenti dei client non supportano i tipi di dati time, date, datetime2 e datetimeoffset. Nella tabella seguente viene illustrato il mapping del tipo tra un'istanza di livello principale di SQL Server e i client legacy.

Tipo di dati di SQL Server	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` oppure `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` oppure `SqString`
date	yyyy-MM-dd	`SQL_WVARCHAR` oppure `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` oppure `SqString`
datetime2	Aaaa-MM-gg HH:mm:ss[.nnnnnnn]	`SQL_WVARCHAR` oppure `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` oppure `SqString`
datetimeoffset	Aaaa-MM-gg HH:mm:ss[.nnnnnnn] [+\|-]hh:mm	`SQL_WVARCHAR` oppure `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` oppure `SqString`

Supporto di Microsoft Fabric

In Microsoft Fabric non è attualmente possibile creare colonne con il tipo di dati datetimeoffset , ma è possibile usare datetimeoffset per convertire i dati con la funzione AT TIME ZONE (Transact-SQL), ad esempio:

SELECT
CAST(CAST('2024-07-03 00:00:00' AS DATETIMEOFFSET) AT TIME ZONE 'Pacific Standard Time' AS datetime2) AS PST

Nel database SQL di Microsoft Fabric è possibile usare la precisione di 7 cifre, ma i dati con mirroring in Fabric OneLake avranno il fuso orario e la settima ora decimali tagliati. Questo tipo di colonna non può essere usato come chiave primaria nelle tabelle del database SQL di Fabric.

Convertire i dati di data e ora

Quando si esegue la conversione in tipi di dati di data e ora, SQL Server rifiuta tutti i valori che non è in grado di riconoscere come date o ore. Per informazioni sull'uso delle CAST funzioni e CONVERT con i dati di data e ora, vedere CAST e CONVERT.

Convertire in tipo di dati datetimeoffset

In questa sezione viene fornito un esempio di aggiornamento dei dati da un tipo di dati senza offset a una nuova colonna del tipo di dati datetimeoffset .

Verificare prima di tutto il nome del fuso orario dalla vista del catalogo di sistema sys.time_zone_info .

SELECT * FROM sys.time_zone_info WHERE name = 'Pacific Standard Time';

Nell'esempio seguente viene utilizzata due volte la sintassi AT TIME ZONE . Il codice di esempio crea una tabella dbo.Audit, aggiunge dati che si estendono su più modifiche all'ora legale e aggiunge una nuova colonna datetimeoffset . Si presuppone che la AuditCreated colonna sia un tipo di dati *datetime2 senza offset e sia stata scritta usando il fuso orario UTC.

Nell'istruzione UPDATE la AT TIME ZONE sintassi aggiunge prima la differenza di fuso orario UTC ai dati della colonna esistenteAuditCreated, quindi converte i dati dall'ora UTC a Pacific Standard Time, modificando correttamente i dati cronologici per ogni intervallo di ora legale precedente nel Stati Uniti.

CREATE TABLE dbo.Audit (AuditCreated DATETIME2(0) NOT NULL);
GO

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('1/1/2024 12:00:00');

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('5/1/2024 12:00:00');

INSERT INTO dbo.Audit (AuditCreated)
VALUES ('12/1/2024 12:00:00');
GO

ALTER TABLE dbo.Audit
ADD AuditCreatedOffset DATETIMEOFFSET(0) NULL;
GO

DECLARE @TimeZone VARCHAR(50);

SELECT @TimeZone = [name]
FROM sys.time_zone_info
WHERE [name] = 'Pacific Standard Time';

UPDATE dbo.Audit
SET AuditCreatedOffset = AuditCreated
    AT TIME ZONE 'UTC' AT TIME ZONE @TimeZone
WHERE AuditCreatedOffset IS NULL;
GO

SELECT *
FROM dbo.Audit;

Il set di risultati è il seguente.

AuditCreated         AuditCreatedOffset
-------------------  --------------------------
2024-01-01 12:00:00  2024-01-01 04:00:00 -08:00
2024-05-01 12:00:00  2024-05-01 05:00:00 -07:00
2024-12-01 12:00:00  2024-12-01 04:00:00 -08:00

Convertire il tipo di dati datetimeoffset in altri tipi di data e ora

Questa sezione descrive il risultato della conversione di un tipo di dati datetimeoffset in altri tipi di dati relativi a data e ora.

Quando si esegue la conversione in data, vengono copiati l'anno, il mese e il giorno. Il codice seguente mostra i risultati della conversione di un valore datetimeoffset(4) in un valore di data .

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10 +01:00';
DECLARE @date DATE = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @date AS 'date';

Il set di risultati è il seguente.

@datetimeoffset                 date
------------------------------ ----------
2025-12-10 12:32:10.0000 +01:0 2025-12-10

Se la conversione è in time(n), vengono copiati l'ora, il minuto, il secondo e i secondi frazionari. Il valore del fuso orario viene troncato. Quando la precisione del valore datetimeoffset(n) è maggiore della precisione del valore time(n), il valore viene arrotondato per enumerazione. Il codice seguente mostra i risultati della conversione di un valore datetimeoffset(4) in un valore time(3).

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @time TIME(3) = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @time AS 'time';

Il set di risultati è il seguente.

@datetimeoffset                 time
------------------------------- ------------
2025-12-10 12:32:10.1237 +01:00 12:32:10.124

Quando si esegue la conversione in datetime, i valori di data e ora vengono copiati e il fuso orario viene troncato. Quando la precisione frazionaria del valore datetimeoffset(n) è maggiore di tre cifre, il valore viene troncato. Il codice seguente mostra i risultati della conversione di un valore datetimeoffset(4) in un valore datetime .

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '12-10-25 12:32:10.1237 +01:0';
DECLARE @datetime DATETIME = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime';

Il set di risultati è il seguente.

@datetimeoffset                datetime
------------------------------ -----------------------
2025-12-10 12:32:10.1237 +01:0 2025-12-10 12:32:10.123

Per le conversioni in smalldatetime, la data e l'ora vengono copiate, i minuti vengono arrotondati rispetto al valore dei secondi e i secondi vengono impostati su 0. Il codice seguente mostra i risultati della conversione di un valore datetimeoffset(3) in un valore smalldatetime .

DECLARE @datetimeoffset DATETIMEOFFSET(3) = '1912-10-25 12:24:32 +10:0';
DECLARE @smalldatetime SMALLDATETIME = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @smalldatetime AS '@smalldatetime';

Il set di risultati è il seguente.

@datetimeoffset                @smalldatetime
------------------------------ -----------------------
1912-10-25 12:24:32.000 +10:00 1912-10-25 12:25:00

Se la conversione è in datetime2(n),la data e l'ora vengono copiate nel valore datetime2 e il fuso orario viene troncato. Quando la precisione del valore datetime2(n) è maggiore della precisione del valore datetimeoffset(n), i secondi frazionari vengono troncati per adattarsi. Il codice seguente mostra i risultati della conversione di un valore datetimeoffset(4) in un valore datetime2(3).

DECLARE @datetimeoffset DATETIMEOFFSET(4) = '1912-10-25 12:24:32.1277 +10:0';
DECLARE @datetime2 DATETIME2(3) = @datetimeoffset;

SELECT @datetimeoffset AS '@datetimeoffset', @datetime2 AS '@datetime2';

Il set di risultati è il seguente.

@datetimeoffset                    @datetime2
---------------------------------- ----------------------
1912-10-25 12:24:32.1277 +10:00    1912-10-25 12:24:32.12

Convertire i valori letterali stringa in datetimeoffset

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 le conversioni esplicite che non specificano uno stile, dai tipi di data e ora ai valori letterali stringa sono nel formato predefinito della sessione corrente. Nella tabella seguente vengono illustrate le regole per la conversione di un valore letterale stringa nel tipo di dati datetimeoffset.

Valore letterale stringa di input	datetimeoffset(n)
`ODBC DATE`	Viene eseguito il mapping dei valori letterali stringa ODBC al tipo di dati datetime. Qualsiasi operazione di assegnazione da `ODBC DATETIME` valori letterali in tipi datetimeoffset causa una conversione implicita tra datetime e questo tipo, come definito dalle regole di conversione.
`ODBC TIME`	Vedere la regola precedente `ODBC DATE`
`ODBC DATETIME`	Vedere la regola precedente `ODBC DATE`
Solo `DATE`	Per impostazione predefinita, la `TIME` parte è `00:00:00`. Il `TIMEZONE` valore predefinito è `+00:00`
Solo `TIME`	Per impostazione predefinita, la `DATE` parte è `1900-1-1`. Il `TIMEZONE` valore predefinito è `+00:00`
Solo `TIMEZONE`	Vengono forniti i valori predefiniti.
`DATE + TIME`	Il `TIMEZONE` valore predefinito è `+00:00`
`DATE + TIMEZONE`	Non consentito
`TIME + TIMEZONE`	Per impostazione predefinita, la parte DATE è `1900-1-1`
`DATE + TIME + TIMEZONE`	Semplice

Condividi tramite

datetimeoffset (Transact-SQL)

Descrizione di datetimeoffset

Formati di valore letterale stringa supportati per datetimeoffset

Differenza di fuso orario

Sintassi di conversione del fuso orario

Conformità agli standard ANSI e ISO 8601

Compatibilità con le versioni precedenti dei client

Supporto di Microsoft Fabric

Convertire i dati di data e ora

Convertire in tipo di dati datetimeoffset

Convertire il tipo di dati datetimeoffset in altri tipi di data e ora

Convertire i valori letterali stringa in datetimeoffset

Commenti e suggerimenti

Risorse aggiuntive

Condividi tramite

datetimeoffset (Transact-SQL)

Descrizione di datetimeoffset

Formati di valore letterale stringa supportati per datetimeoffset

Differenza di fuso orario

Sintassi di conversione del fuso orario

Conformità agli standard ANSI e ISO 8601

Compatibilità con le versioni precedenti dei client

Supporto di Microsoft Fabric

Convertire i dati di data e ora

Convertire in tipo di dati datetimeoffset

Convertire il tipo di dati datetimeoffset in altri tipi di data e ora

Convertire i valori letterali stringa in datetimeoffset

Contenuto correlato

Commenti e suggerimenti

Risorse aggiuntive