datetimeoffset (Transact-SQL)

Article
09/24/2024

S’applique à : SQL Server Base de données Azure SQL Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Point de terminaison d'analyse SQL dans Microsoft Fabric Entrepôt dans Microsoft Fabric Base de données SQL dans Microsoft Fabric

Définit une date combinée à une heure d’une journée basée sur une horloge de 24 heures, comme datetime2, et ajoute une prise en charge du fuseau horaire en fonction du temps universel coordonné (UTC).

Description de datetimeoffset

Propriété	Valeur
Syntaxe	*DATETIMEOFFSET [ ( précision* fractionnaire en secondes ) ]**
Utilisation	`DECLARE @MyDatetimeoffset DATETIMEOFFSET(7);` `CREATE TABLE Table1 (Column1 DATETIMEOFFSET(7));`
Formats littéraux de chaîne par défaut (utilisés pour le client de bas niveau)	aaaa-MM-dd HH :mm :ss[.nnnnnnn] [{+\|-}hh :mm] Pour plus d’informations, consultez la section Compatibilité descendante pour les clients de bas niveau qui suivent.
Plage de dates	`0001-01-01` à `9999-12-31` Du 1er janvier de l’an 1 au 31 décembre 9999
Intervalle de temps	`00:00:00` à `23:59:59.9999999`
Plage de décalage de fuseau horaire	`-14:00` à `+14:00`
Plages d’éléments	`yyyy` est de quatre chiffres, allant de `0001` à `9999`, qui représentent une année. `MM` est de deux chiffres, allant de `01` à `12`, qui représentent un mois dans l’année spécifiée. `dd` est de deux chiffres, allant de `01` à `31` selon le mois, qui représentent un jour du mois spécifié. `HH` est deux chiffres, allant de `00` à `23`, qui représentent l’heure. `mm` est de deux chiffres, allant de `00` à `59`, qui représentent la minute. `ss` est deux chiffres, allant de `00` à `59`, qui représentent la seconde. n est égal à zéro à sept chiffres, allant de `0` à `9999999`, qui représentent les fractions de seconde. `hh` est deux chiffres allant de `-14` à `+14`. `mm` est deux chiffres allant de `00` à `59`.
Longueur du caractère	26 positions minimales (aaaa-MM-jj HH :mm :ss {+\|-}hh :mm) à 34 maximum (aaaa-MM-jj HH :mm :ss.nnnnnnn {+-}hh :mm)
Précision, échelle	Consultez le tableau suivant.
Taille de stockage	10 octets, fixe est la valeur par défaut avec la valeur par défaut de 100-ns fractionnaire seconde précision.
Précision	100 nanosecondes
Valeur par défaut	`1900-01-01 00:00:00 00:00`
Calendar	Grégorien
Précision fractionnaire fractionnaire définie par l’utilisateur	Oui
Décalage de fuseau horaire prenant en charge et conservation	Oui
Prise en charge de l’enregistrement d’été	Non

Échelle spécifiée	Résultat (précision, échelle)	Longueur de colonne (octets)	Précision en fractions de seconde
datetimeoffset	(34, 7)	10	7
datetimeoffset(0)	(26, 0)	8	0 à 2
datetimeoffset(1)	(28, 1)	8	0 à 2
datetimeoffset(2)	(29, 2)	8	0 à 2
datetimeoffset(3)	(30, 3)	9	3 à 4
datetimeoffset(4)	(31, 4)	9	3 à 4
datetimeoffset(5)	(32, 5)	10	5 à 7
datetimeoffset(6)	(33, 6)	10	5 à 7
datetimeoffset(7)	(34, 7)	10	5 à 7

Formats de littéraux de chaîne pris en charge pour datetimeoffset

Le tableau suivant répertorie les formats de littéraux de chaîne ISO 8601 pris en charge pour datetimeoffset. Pour plus d’informations sur les formats alphabétiques, numériques, nonparés et d’heure pour les parties de date et d’heure de datetimeoffset, consultez la date (Transact-SQL) et l’heure (Transact-SQL) .

ISO 8601	Description
aaaa-MM-ddTHH :mm :ss[.nnnnnnn][{+\|-}hh :mm]	Ces deux formats ne sont pas affectés par les paramètres régionaux de session et `SET DATEFORMAT` les `SET LANGUAGE` paramètres régionaux de session. Les espaces ne sont pas autorisés entre le datetimeoffset et les parties datetime .
aaaa-MM-ddTHH :mm :ss[.nnnnnnn]Z (UTC)	Ce format par définition ISO indique que la partie datetime doit être exprimée dans le fuseau horaire UTC. Par exemple, `1999-12-12 12:30:30.12345 -07:00` doit être représenté en tant que `1999-12-12 19:30:30.12345Z`.

L’exemple suivant compare les résultats de la conversion d’une chaîne en chaque type de données date et 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';

Voici le jeu de résultats.

Type de données	Sortie
`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`

Décalage de fuseau horaire

Un décalage de fuseau horaire spécifie le décalage de la zone de fuseau horaire UTC pour une valeur time ou datetime. Le décalage de fuseau horaire peut être représenté sous la forme [+|-] hh:mm:

hh est deux chiffres allant de 00 à 14 et représentant le nombre d’heures dans le décalage de fuseau horaire.
mm est de deux chiffres, allant de 00 à 59, qui représentent le nombre de minutes supplémentaires dans le décalage de fuseau horaire.
+ (plus) ou - (moins) est le signe obligatoire pour un décalage de fuseau horaire. Ce signe indique si le décalage de fuseau horaire est ajouté ou soustrait de l’heure UTC pour obtenir l’heure locale. La plage valide du décalage de fuseau horaire se situe entre -14:00 et +14:00.

La plage de décalage de fuseau horaire suit la norme XML W3C pour la définition de schéma XSD, et est légèrement différente de la définition standard SQL 2003, 12:59 à +14:00.

Le paramètre de type facultatif précision à la fraction de seconde spécifie le nombre de chiffres pour la partie fractionnaire des secondes. Cette valeur peut être un entier avec 0 à 7 chiffres (100 nanosecondes). La précision fractionnaire par défaut est de 100 ns (sept chiffres pour la partie fractionnaire des secondes).

Les données sont stockées dans la base de données, puis traitées, comparées, triées et indexées sur le serveur comme au format UTC. Le décalage de fuseau horaire est conservé dans la base de données pour la récupération.

Le décalage de fuseau horaire donné est supposé être conscient de l’heure d’été (DST) et ajusté pour toute datetime donnée qui se trouve dans la période DST.

Pour le type datetimeoffset , à la fois UTC et local (au décalage de fuseau horaire persistant ou converti), la valeur datetime est validée lors de l’insertion, de la mise à jour, de l’arithmétique, de la conversion ou de l’affectation d’opérations. La détection d’une valeur datetime utc ou locale non valide (au décalage de fuseau horaire persistant ou converti) génère une erreur de valeur non valide. Par exemple, 9999-12-31 10:10:00 est valide au format UTC, mais dépasse l’heure locale pour le décalage +13:50de fuseau horaire.

Syntaxe de conversion de fuseau horaire

SQL Server 2016 (13.x) a introduit la syntaxe pour faciliter les conversions de fuseau horaire universels prenant en charge l’été AT TIME ZONE . Cette syntaxe est particulièrement utile lors de la conversion de données sans décalages de fuseau horaire, en données avec des décalages de fuseau horaire. Pour convertir en valeur datetimeoffset correspondante dans un fuseau horaire cible, consultez AT TIME ZONE.

Conformité ANSI et ISO 8601

Les sections de conformité ANSI et ISO 8601 des articles de date et d’heure s’appliquent à datetimeoffset.

Compatibilité descendante pour les clients de bas niveau

Certains clients de bas niveau ne prennent pas en charge les types de données time, date, datetime2 et datetimeoffset. Le tableau suivant présente le type de mappage entre une instance de haut niveau de SQL Server et des clients de bas niveau.

Type de données SQL Server	Format de littéral de chaîne par défaut passé au client de bas niveau	ODBC de bas niveau	OLEDB de bas niveau	JDBC de bas niveau	SQLCLIENT de bas niveau
time	HH :mm :ss[.nnnnnnn]	`SQL_WVARCHAR` ou `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` ou `SqString`
date	yyyy-MM-dd	`SQL_WVARCHAR` ou `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` ou `SqString`
datetime2	aaaa-MM-jj HH :mm :ss[.nnnnnnn]	`SQL_WVARCHAR` ou `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` ou `SqString`
datetimeoffset	aaaa-MM-dd HH :mm :ss[.nnnnnnn] [+\|-]hh :mm	`SQL_WVARCHAR` ou `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` ou `SqString`

Prise en charge de Microsoft Fabric

Dans Microsoft Fabric, vous ne pouvez pas créer de colonnes avec le type de données datetimeoffset , mais vous pouvez utiliser datetimeoffset pour convertir des données avec la fonction AT TIME ZONE (Transact-SQL), par exemple :

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

Dans la base de données Microsoft Fabric SQL : la précision de 7 chiffres peut être utilisée, mais les données mises en miroir dans Fabric OneLake auront le fuseau horaire et la septième fois décimales. Ce type de colonne ne peut pas être utilisé comme clé primaire dans les tables de la base de données SQL Fabric.

Convertir des données de date et d’heure

Lorsque vous convertissez en types de données date et heure, SQL Server rejette toutes les valeurs qu’il ne peut pas reconnaître comme des dates ou des heures. Pour plus d’informations sur l’utilisation des CAST fonctions avec CONVERT des données de date et d’heure, consultez CAST et CONVERT.

Convertir en type de données datetimeoffset

Cette section fournit un exemple de mise à jour des données à partir d’un type de données sans décalage vers une nouvelle colonne de type de données datetimeoffset .

Tout d’abord, vérifiez le nom du fuseau horaire à partir de l’affichage catalogue système sys.time_zone_info .

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

L’exemple suivant utilise deux fois la syntaxe AT TIME ZONE . L’exemple de code crée une table dbo.Audit, ajoute des données qui s’étendent sur plusieurs modifications d’heure d’été et ajoute une nouvelle colonne datetimeoffset . Nous partons du principe que la AuditCreated colonne est un type de données *datetime2 sans décalages et qu’elle a été écrite à l’aide du fuseau horaire UTC.

Dans l’instructionUPDATE, la AT TIME ZONE syntaxe ajoute d’abord un décalage de fuseau horaire UTC aux données de colonne existantesAuditCreated, puis convertit les données utc en Pacific Standard Timeajustement correct des données historiques pour chaque plage de temps d’été passée dans le États-Unis.

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;

Voici le jeu de résultats.

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

Convertir le type de données datetimeoffset en d’autres types de date et d’heure

Cette section décrit ce qui se produit quand un type de données datetimeoffset est converti en d’autres types de données date et time.

Lorsque vous convertissez en date, l’année, le mois et le jour sont copiés. Le code suivant montre les résultats de la conversion d’une valeur datetimeoffset(4) en valeur de date .

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

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

Voici le jeu de résultats.

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

Si la conversion est en time(n),l’heure, la minute, la seconde et les fractions de seconde sont copiées. La valeur de fuseau horaire est tronquée. Lorsque la précision de la valeur datetimeoffset(n) est supérieure à la précision de la valeur time(n), la valeur est arrondie. Le code suivant montre les résultats de la conversion d’une valeur datetimeoffset(4) en valeur 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';

Voici le jeu de résultats.

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

Lorsque vous convertissez en datetime, les valeurs de date et d’heure sont copiées et le fuseau horaire est tronqué. Lorsque la précision fractionnelle de la valeur datetimeoffset(n) est supérieure à trois chiffres, la valeur est tronquée. Le code suivant montre les résultats de la conversion d’une valeur datetimeoffset(4) en valeur 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';

Voici le jeu de résultats.

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

Dans le cas d’une conversion en smalldatetime, la date et les heures sont copiées. Les minutes sont arrondies selon la valeur des secondes et les secondes sont définies sur 0. Le code suivant montre les résultats de la conversion d’une valeur datetimeoffset(3) en valeur smalldatetime .

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

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

Voici le jeu de résultats.

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

Si la conversion est en datetime2(n), la date et l’heure sont copiées dans la valeur datetime2 et le fuseau horaire est tronqué. Lorsque la précision de la valeur datetime2(n) est supérieure à la précision de la valeur datetimeoffset(n), les fractions de seconde sont tronquées pour s’adapter. Le code suivant montre les résultats de la conversion d’une valeur datetimeoffset(4) en valeur 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';

Voici le jeu de résultats.

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

Convertir des littéraux de chaîne en datetimeoffset

Les conversions de littéraux de chaîne en types de date et d'heure sont autorisées si toutes les parties des chaînes sont dans des formats valides. Sinon, une erreur d'exécution est déclenchée. Les conversions implicites ou les conversions explicites qui ne spécifient pas de style, des types de date et d’heure aux littéraux de chaîne sont au format par défaut de la session active. Le tableau suivant montre les règles de conversion d’un littéral de chaîne en type de données datetimeoffset.

Littéral de chaîne d'entrée	datetimeoffset(n)
`ODBC DATE`	Les littéraux de chaîne ODBC sont mappés au type de données datetime. Toute opération d’affectation de `ODBC DATETIME` littéraux en types datetimeoffset entraîne une conversion implicite entre datetime et ce type, tel que défini par les règles de conversion.
`ODBC TIME`	Voir la règle précédente `ODBC DATE`
`ODBC DATETIME`	Voir la règle précédente `ODBC DATE`
`DATE` uniquement	La `TIME` partie est par défaut `00:00:00`. Valeurs `TIMEZONE` par défaut `+00:00`
`TIME` uniquement	La `DATE` partie est par défaut `1900-1-1`. Valeurs `TIMEZONE` par défaut `+00:00`
`TIMEZONE` uniquement	Les valeurs par défaut sont fournies
`DATE + TIME`	Valeurs `TIMEZONE` par défaut `+00:00`
`DATE + TIMEZONE`	Non autorisé
`TIME + TIMEZONE`	La partie DATE est par défaut `1900-1-1`
`DATE + TIME + TIMEZONE`	Simple

Partager via

datetimeoffset (Transact-SQL)

Description de datetimeoffset

Formats de littéraux de chaîne pris en charge pour datetimeoffset

Décalage de fuseau horaire

Syntaxe de conversion de fuseau horaire

Conformité ANSI et ISO 8601

Compatibilité descendante pour les clients de bas niveau

Prise en charge de Microsoft Fabric

Convertir des données de date et d’heure

Convertir en type de données datetimeoffset

Convertir le type de données datetimeoffset en d’autres types de date et d’heure

Convertir des littéraux de chaîne en datetimeoffset

Commentaires

Ressources supplémentaires

Partager via

datetimeoffset (Transact-SQL)

Description de datetimeoffset

Formats de littéraux de chaîne pris en charge pour datetimeoffset

Décalage de fuseau horaire

Syntaxe de conversion de fuseau horaire

Conformité ANSI et ISO 8601

Compatibilité descendante pour les clients de bas niveau

Prise en charge de Microsoft Fabric

Convertir des données de date et d’heure

Convertir en type de données datetimeoffset

Convertir le type de données datetimeoffset en d’autres types de date et d’heure

Convertir des littéraux de chaîne en datetimeoffset

Contenu connexe

Commentaires

Ressources supplémentaires