datetimeoffset (Transact-SQL)

Artículo
11/19/2024

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Punto de conexión de análisis SQL en Microsoft Fabric Warehouse en Microsoft Fabric Base de datos SQL de Microsoft Fabric

Define una fecha que se combina con una hora de un día basada en un reloj de 24 horas, como datetime2, y agrega reconocimiento de zona horaria en función de la hora universal coordinada (UTC).

Descripción de datetimeoffset

Propiedad	Value
Sintaxis	*DATETIMEOFFSET [ ( precisión* de fracciones de segundos ) ]**
Uso	`DECLARE @MyDatetimeoffset DATETIMEOFFSET(7);` `CREATE TABLE Table1 (Column1 DATETIMEOFFSET(7));`
Formatos literales de cadena predeterminados (usados para el cliente de nivel anterior)	aaaa-MM-dd HH:mm:ss[.nnnnnnn] [{+\|-}hh:mm] Para obtener más información, consulte la sección Compatibilidad con versiones anteriores para clientes de nivel inferior que se muestra a continuación.
Intervalo de fechas	`0001-01-01` hasta `9999-12-31` Del 1 de enero del año 1 E. C. al 31 de diciembre de 9999 E. C.
Intervalo de tiempo	`00:00:00` hasta `23:59:59.9999999`
Intervalo de desplazamiento de zona horaria	`-14:00` hasta `+14:00`
Intervalos de elementos	`yyyy` es de cuatro dígitos, que van desde `0001` hasta `9999`, que representan un año. `MM` es dos dígitos, que van de `01` a `12`, que representan un mes en el año especificado. `dd` es de dos dígitos, que van desde `01` hasta `31` según el mes, que representan un día del mes especificado. `HH` es de dos dígitos, que van de `00` a `23`, que representan la hora. `mm` es de dos dígitos, que van de `00` a `59`, que representan el minuto. `ss` es de dos dígitos, que van de `00` a `59`, que representan el segundo. n es cero a siete dígitos, que van de `0` a `9999999`, que representan las fracciones de segundos. `hh` es de dos dígitos que van de `-14` a `+14`. `mm` es de dos dígitos que van de `00` a `59`.
Longitud de caracteres	26 posiciones mínimas (aaaa-MM-dd HH:mm:ss {+\|-}hh:mm) a 34 como máximo (aaaa-MM-dd HH:mm:ss.nnnn {+\|-}hh:mm)
Precisión, escala	Vea la siguiente tabla.
Tamaño de almacenamiento	10 bytes, fijo es el valor predeterminado con el valor predeterminado de precisión de 100 ns fracciones de segundo.
Precisión	100 nanosegundos
Valor predeterminado	`1900-01-01 00:00:00 00:00`
Calendario	Gregoriano
Precisión de segundo fraccionario definida por el usuario	Sí
Reconocimiento y conservación del desplazamiento de zona horaria	Yes
Reconocimiento del horario de verano	No

Escala especificada	Resultado (precisión, escala)	Longitud de la columna (bytes)	Precisión de fracciones de segundo
datetimeoffset	(34, 7)	10	7
datetimeoffset(0)	(26, 0)	8	De 0 a 2
datetimeoffset(1)	(28, 1)	8	De 0 a 2
datetimeoffset(2)	(29, 2)	8	De 0 a 2
datetimeoffset(3)	(30, 3)	9	De 3 a 4
datetimeoffset(4)	(31, 4)	9	De 3 a 4
datetimeoffset(5)	(32, 5)	10	De 5 a 7
datetimeoffset(6)	(33, 6)	10	De 5 a 7
datetimeoffset(7)	(34, 7)	10	De 5 a 7

Formatos de literales de cadena admitidos para datetimeoffset

En esta tabla se enumeran los formatos de literales de cadena ISO 8601 admitidos para datetimeoffset. Para obtener información sobre los formatos alfabéticos, numéricos, no separados y de hora para las partes de fecha y hora de datetimeoffset, vea fecha (Transact-SQL) y hora (Transact-SQL).

ISO 8601	Descripción
aaaa-MM-ddTHH:mm:ss[.nnnnnnn][{+\|-}hh:mm]	Estos dos formatos no se ven afectados por la configuración regional de la `SET LANGUAGE` sesión y `SET DATEFORMAT` . No se permiten espacios entre las partes datetimeoffset y datetime .
aaaa-MM-ddTHH:mm:ss[.nnnnnnn]Z (UTC)	Este formato por definición de ISO indica que la parte datetime se debería expresar en Hora universal coordinada (UTC). Por ejemplo, `1999-12-12 12:30:30.12345 -07:00` debe representarse como `1999-12-12 19:30:30.12345Z`.

En el siguiente ejemplo se comparan los resultados de convertir una cadena a cada tipo de datos date y 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';

Este es el conjunto de resultados.

Tipo de datos	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`

Ajuste de zona horaria

El desplazamiento de zona horaria especifica el desplazamiento de zona a partir de Hora UTC para un valor time o datetime. El ajuste de zona horaria se puede representar como [+ | -] hh:mm:

hh es dos dígitos que van de 00 a 14 y representan el número de horas en el desplazamiento de zona horaria.
mm es de dos dígitos, que van de 00 a 59, que representan el número de minutos adicionales en el desplazamiento de zona horaria.
+ (más) o - (menos) es el signo obligatorio para un desplazamiento de zona horaria. Este signo indica si el desplazamiento de zona horaria se agrega o resta de la hora UTC para obtener la hora local. El intervalo válido de ajuste de zona horaria es de -14:00 a +14:00.

El intervalo de desplazamiento de zona horaria sigue el estándar XML W3C para la definición de esquema XSD y es ligeramente diferente de la definición estándar de SQL 2003 a 12:59 +14:00.

El parámetro de tipo opcional precisión de fracciones de segundo especifica el número de dígitos para la parte fraccionaria de los segundos. Este valor puede ser un entero con 0 a 7 (100 nanosegundos). La precisión predeterminada de fracciones de segundos es 100 ns (siete dígitos para la parte fraccionaria de los segundos).

Los datos se almacenan en la base de datos y se procesan, comparan, ordena e indizan en el servidor como en UTC. El desplazamiento de zona horaria se conserva en la base de datos para su recuperación.

Se supone que el desplazamiento de zona horaria dado es compatible con el horario de verano (DST) y se ajusta para cualquier fecha y hora determinada que se encuentra en el período DST.

Para el tipo datetimeoffset, el valor datetimeoffset se valida durante las operaciones de inserción, actualización, aritmética, conversión o asignación del valor de fecha y hora persistente o convertido. La detección de cualquier valor UTC o local no válido (en el desplazamiento de zona horaria persistente o convertido) genera un error de valor no válido. Por ejemplo, 9999-12-31 10:10:00 es válido en UTC, pero se desborda en la hora local hasta el desplazamiento +13:50de zona horaria .

Sintaxis de conversión de zona horaria

SQL Server 2016 (13.x) introdujo la AT TIME ZONE sintaxis para facilitar las conversiones de zona horaria universal compatibles con el horario de verano. Esta sintaxis es especialmente útil al convertir datos sin desplazamientos de zona horaria, a datos con desplazamientos de zona horaria. Para convertir a un valor datetimeoffset correspondiente en una zona horaria de destino, consulte AT TIME ZONE.

Compatibilidad con ANSI e ISO 8601

Las secciones de cumplimiento de ANSI e ISO 8601 de los artículos de fecha y hora se aplican a datetimeoffset.

Compatibilidad con versiones anteriores de clientes de niveles inferiores

Algunos clientes de nivel inferior no admiten los tipos de datos time, date, datetime2 y datetimeoffset. En la tabla siguiente se muestra la asignación de tipo entre una instancia de nivel superior de SQL Server y los clientes de nivel inferior.

Tipo de datos de SQL Server	El formato del literal de cadena predeterminado se pasó al cliente de nivel inferior	ODBC de nivel inferior	OLEDB de nivel inferior	JDBC de nivel inferior	SQLCLIENT de nivel inferior
time	HH:mm:ss[.nnnnnnnn]	`SQL_WVARCHAR` o `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` o `SqString`
date	yyyy-MM-dd	`SQL_WVARCHAR` o `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` o `SqString`
datetime2	aaaa-MM-dd HH:mm:ss[.nnnnnnn]	`SQL_WVARCHAR` o `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` o `SqString`
datetimeoffset	aaaa-MM-dd HH:mm:ss[.nnnnnnn] [+\|-]hh:mm	`SQL_WVARCHAR` o `SQL_VARCHAR`	`DBTYPE_WSTRor DBTYPE_STR`	`Java.sql.String`	`String` o `SqString`

Compatibilidad con Microsoft Fabric

En Microsoft Fabric, actualmente no puede crear columnas con el tipo de datos datetimeoffset , pero puede usar datetimeoffset para convertir datos con la función AT TIME ZONE (Transact-SQL), por ejemplo:

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

En la base de datos SQL de Microsoft Fabric: se puede usar la precisión de 7 dígitos, pero los datos reflejados en Fabric OneLake tendrían la zona horaria y el séptimo decimal de hora recortados. Este tipo de columna no se puede usar como clave principal en tablas de Fabric SQL Database.

Convierte datos de fecha y hora

Al convertir a tipos de datos de fecha y hora, SQL Server rechaza todos los valores que no pueden reconocer como fechas o horas. Para obtener información sobre el uso de las CAST funciones y con datos de fecha y hora, consulte CAST y CONVERT CONVERT.

Conversión al tipo de datos datetimeoffset

En esta sección se proporciona un ejemplo de actualización de datos de un tipo de datos sin desplazamientos a una nueva columna de tipo de datos datetimeoffset .

En primer lugar, compruebe el nombre de la zona horaria de la vista de catálogo del sistema sys.time_zone_info .

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

En el ejemplo siguiente se usa la sintaxis AT TIME ZONE dos veces. El código de ejemplo crea una tabla dbo.Audit, agrega datos que abarcan varios cambios en el horario de verano y agrega una nueva columna datetimeoffset . Se supone que la AuditCreated columna es un tipo de datos *datetime2 sin desplazamientos y se escribió mediante la zona horaria UTC.

En la UPDATE instrucción , la AT TIME ZONE sintaxis agrega primero el desplazamiento de zona horaria UTC a los datos de columna existentes AuditCreated y, a continuación, convierte los datos de UTC en Pacific Standard Time, ajustando correctamente los datos históricos de cada intervalo de tiempo de verano anterior en el Estados Unidos.

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;

Este es el conjunto de resultados.

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 el tipo de datos datetimeoffset a otros tipos de fecha y hora

En esta tabla se describe lo que ocurre cuando un tipo de datos datetimeoffset se convierte a otros tipos de datos de fecha y hora.

Al convertir a fecha, se copian el año, el mes y el día. En el código siguiente se muestran los resultados de convertir un valor datetimeoffset(4) en un valor de fecha .

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

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

Este es el conjunto de resultados.

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

Si la conversión es a time(n), se copian la hora, el minuto, el segundo y las fracciones de segundo. Se trunca el valor de zona horaria. Cuando la precisión del valor datetimeoffset(n) es mayor que la precisión del valor time(n), el valor se redondea hacia arriba. En el código siguiente se muestran los resultados de convertir un valor datetimeoffset(4) en un valor 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';

Este es el conjunto de resultados.

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

Al convertir a datetime, se copian los valores de fecha y hora y se trunca la zona horaria. Cuando la precisión fraccionaria del valor datetimeoffset(n) es mayor que tres dígitos, el valor se trunca. El código siguiente muestra los resultados de convertir un valor datetimeoffset(4) en un valor 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';

Este es el conjunto de resultados.

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

Para las conversiones a smalldatetime, se copia la fecha y la hora. Los minutos se redondean hacia arriba con respecto al valor de los segundos y los segundos se establecen en 0. El código siguiente muestra los resultados de convertir un valor datetimeoffset(3) en un valor smalldatetime .

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

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

Este es el conjunto de resultados.

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

Si la conversión es a datetime2(n), la fecha y hora se copian en el valor datetime2 y la zona horaria se trunca. Cuando la precisión del valor datetime2(n) es mayor que la precisión del valor datetimeoffset(n), las fracciones de segundos se truncan para ajustarse. El código siguiente muestra los resultados de convertir un valor datetimeoffset(4) en un valor 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';

Este es el conjunto de resultados.

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

Convertir literales de cadena en datetimeoffset

Las conversiones de literales de cadena en tipos de fecha y hora son posibles cuando todas las partes de las cadenas están en formatos válidos. En caso contrario, se generará un error en el tiempo de ejecución. Las conversiones implícitas o las conversiones explícitas que no especifican un estilo, de tipos de fecha y hora a literales de cadena están en el formato predeterminado de la sesión actual. En esta tabla se muestran las reglas para convertir un literal de cadena al tipo de datos datetimeoffset.

Literal de cadena de entrada	datetimeoffset(n)
`ODBC DATE`	Los literales de cadena de ODBC se asignan al tipo de datos datetime. Cualquier operación de asignación de `ODBC DATETIME` literales en tipos datetimeoffset provoca una conversión implícita entre datetime y este tipo, tal como se define en las reglas de conversión.
`ODBC TIME`	Consulte la regla anterior.`ODBC DATE`
`ODBC DATETIME`	Consulte la regla anterior.`ODBC DATE`
Solo `DATE`	El valor predeterminado de la `TIME` parte es `00:00:00`. Valores `TIMEZONE` predeterminados para `+00:00`
Solo `TIME`	El valor predeterminado de la `DATE` parte es `1900-1-1`. Valores `TIMEZONE` predeterminados para `+00:00`
Solo `TIMEZONE`	Se proporcionan los valores predeterminados.
`DATE + TIME`	Valores `TIMEZONE` predeterminados para `+00:00`
`DATE + TIMEZONE`	No permitida
`TIME + TIMEZONE`	El valor predeterminado de la parte DATE es `1900-1-1`
`DATE + TIME + TIMEZONE`	Trivial

Compartir a través de

datetimeoffset (Transact-SQL)

Descripción de datetimeoffset

Formatos de literales de cadena admitidos para datetimeoffset

Ajuste de zona horaria

Sintaxis de conversión de zona horaria

Compatibilidad con ANSI e ISO 8601

Compatibilidad con versiones anteriores de clientes de niveles inferiores

Compatibilidad con Microsoft Fabric

Convierte datos de fecha y hora

Conversión al tipo de datos datetimeoffset

Convertir el tipo de datos datetimeoffset a otros tipos de fecha y hora

Convertir literales de cadena en datetimeoffset

Comentarios

Recursos adicionales

Compartir a través de

datetimeoffset (Transact-SQL)

Descripción de datetimeoffset

Formatos de literales de cadena admitidos para datetimeoffset

Ajuste de zona horaria

Sintaxis de conversión de zona horaria

Compatibilidad con ANSI e ISO 8601

Compatibilidad con versiones anteriores de clientes de niveles inferiores

Compatibilidad con Microsoft Fabric

Convierte datos de fecha y hora

Conversión al tipo de datos datetimeoffset

Convertir el tipo de datos datetimeoffset a otros tipos de fecha y hora

Convertir literales de cadena en datetimeoffset

Contenido relacionado

Comentarios

Recursos adicionales