Pregunta DateTime2 vs DateTime en SQL Server


Cúal:

es el ¿manera recomendada de almacenar fecha y hora en SQL Server 2008+?

Soy consciente de las diferencias en precisión (y espacio de almacenamiento probablemente), pero ignorando esas por ahora, ¿hay algún documento de mejores prácticas sobre cuándo usar qué, o quizás deberíamos simplemente usarlo? datetime2 ¿solamente?


620
2017-08-26 11:45


origen


Respuestas:


La documentación de MSDN para fecha y hora recomienda usar datetime2. Aquí está su recomendación:

Utilizar el time, date, datetime2 y    datetimeoffset tipos de datos para nuevos   trabajo. Estos tipos se alinean con el SQL   Estándar. Son más portátiles.    time, datetime2 y datetimeoffset   proporcionar más segundos de precisión.    datetimeoffset proporciona la zona horaria   soporte para despliegue global   aplicaciones.

datetime2 tiene un rango de fechas más grande, una precisión fraccional predeterminada más grande y una precisión opcional especificada por el usuario. Además, dependiendo de la precisión especificada por el usuario, puede usar menos almacenamiento.


513
2017-12-10 21:17



DATETIME2 tiene un rango de fechas de "0001/01/01" a "9999/12/31" mientras que el DATETIME tipo solo admite el año 1753-9999.

Además, si es necesario, DATETIME2 puede ser más preciso en términos de tiempo; DATETIME está limitado a 3 1/3 milisegundos, mientras DATETIME2 puede ser preciso hasta 100ns.

Ambos tipos se asignan a System.DateTime en .NET - no hay diferencia allí.

Si tiene la opción, le recomendaría usar DATETIME2 cuando sea posible. No veo ningún beneficio usando DATETIME (excepto por la compatibilidad con versiones anteriores): tendrás menos problemas (con fechas fuera de rango y problemas como ese).

Además: si solo necesita la fecha (sin parte de tiempo), use FECHA - es tan bueno como DATETIME2 ¡y también te ahorra espacio! :-) Lo mismo vale solo por tiempo: use TIME. ¡Para eso están estos tipos!


425
2017-08-26 11:56



datetime2 gana en la mayoría de los aspectos excepto (compatibilidad con aplicaciones antiguas)

  1. más grande rango de valores 
  2. mejor Exactitud
  3. menor Espacio de almacenamiento  (si se especifica una precisión opcional especificada por el usuario)

SQL Date and time data types compare - datetime,datetime2,date,TIME

tenga en cuenta los siguientes puntos

  • Sintaxis
    • datetime2 [(precisión de segundos fraccionarios => mirar por debajo del tamaño de almacenamiento)]
  • Escala de precisión
    • 0 a 7 dígitos, con una precisión de 100ns.
    • La precisión predeterminada es de 7 dígitos.
  • Tamaño de almacenamiento
    • 6 bytes para precisión menor que 3;
    • 7 bytes para precisión 3 y 4.
    • Toda la otra precisión requiere 8 bytes.
  • DateTime2 (3) tiene el mismo número de dígitos que DateTime, pero usa 7 bytes de almacenamiento en lugar de 8 bytes (SQLHINTS- DateTime Vs DateTime2)
  • Encuentre más en datetime2 (artículo de MSDN de Transact-SQL)

fuente de imagen : Kit de entrenamiento a ritmo propio MCTS (Examen 70-432): Microsoft SQL Server 2008 - Implementación y mantenimiento Capítulo 3: Tablas -> Lección 1: Crear tablas -> página 66


152
2017-09-11 06:52



Estoy de acuerdo con @marc_s y @Adam_Poward - DateTime2 es el método preferido para seguir adelante. Tiene un rango más amplio de fechas, mayor precisión y utiliza igual o menos almacenamiento (dependiendo de la precisión).

Una cosa que la discusión se perdió, sin embargo ...
@Marc_s dice: Both types map to System.DateTime in .NET - no difference there. Esto es correcto, sin embargo, lo inverso no es verdad... y es importante al realizar búsquedas en el rango de fechas (por ejemplo, "buscar todos los registros modificados el 5/5/2010").

La versión de .NET Datetime tiene un rango y precisión similares a DateTime2. Cuando mapea un .net Datetime hasta el viejo SQL DateTime un se produce el redondeo implícito. El viejo SQL DateTime tiene una precisión de 3 milisegundos. Esto significa que 11:59:59.997 es lo más cerca que puedes llegar al final del día. Cualquier cosa más alta se redondea al día siguiente.

Prueba esto :

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

Evitar este redondeo implícito es una razón importante para pasar a DateTime2. El redondeo implícito de fechas claramente causa confusión:


101
2018-03-08 18:00



DateTime2 causa estragos si usted es un desarrollador de Access que intenta escribir Now () en el campo en cuestión. Acabo de hacer una migración de Access -> SQL 2008 R2 y poner todos los campos de fecha y hora como DateTime2. Anexar un registro con Now () como el valor bombardeado. Estuvo bien el 1/1/2012 2:53:04 PM, pero no el 1/10/2012 2:53:04 PM.

Una vez el personaje hizo la diferencia. Espero que ayude a alguien.


13
2018-02-13 19:52



Aquí hay un ejemplo que le mostrará las diferencias en el tamaño de almacenamiento (bytes) y la precisión entre smalldatetime, datetime, datetime2 (0) y datetime2 (7):

DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp

que devuelve

sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8

Así que si quiero para almacenar información hasta el segundo - pero no a la milésima de segundo - puedo ahorrar 2 bytes cada uno si uso datetime2 (0) en vez de datetime o datetime2 (7).


12
2017-09-11 17:29



Casi todas las Respuestas y Comentarios han sido pesados ​​sobre los Profesionales y luz sobre los Cons. Aquí hay una recapitulación de todos los pros y contras hasta el momento más algunas contras cruciales (en el n. ° 2 a continuación). Solo he mencionado una vez o ninguna.

  1. PROS:

1.1. Más ISO conforme (ISO 8601) (aunque no sé cómo esto entra en juego en la práctica).

1.2. Más gama (1/1/0001 al 12/31/9999 vs 1/1 / 1753-12 / 31/9999) (aunque el rango adicional, todo antes del año 1753, es probable que no se use, excepto por ej., en aplicaciones históricas, astronómicas, geológicas, etc.).

1.3. Concuerda exactamente con el rango de .NET DateTime El rango del tipo (aunque ambos se convierten hacia adelante y hacia atrás sin codificación especial si los valores están dentro del rango y la precisión del tipo de destino excepto para Con # 2.1 a continuación, de lo contrario se producirá un error / redondeo).

1.4. Más precisión (también conocido como 100 nanosegundos 0.000,000,1 seg. Vs. 3,33 milisegundos aka 0.003,33 seg.) (Aunque la precisión adicional no es probable que se use, excepto por ej., En aplicaciones científicas de ingeniería /).

1.5. Cuando se configura para similar (como en 1 milisegundo no "igual" (como en 3.33 milisegundos) como Iman Abidi ha afirmado) precisión como DateTime, usa menos espacio (7 vs. 8 bytes), pero luego, por supuesto, estarías perdiendo el beneficio de precisión que probablemente sea uno de los dos (el otro sea el rango) más promocionado, aunque probablemente sea beneficios innecesarios).

  1. CONTRAS:

2.1. Al pasar un parámetro a .NET SqlCommand, debes especificar System.Data.SqlDbType.DateTime2 si puede estar pasando un valor fuera del Servidor SQL DateTimeRango y / o precisión de este, ya que por defecto System.Data.SqlDbType.DateTime.

2.2. No se puede convertir de forma implícita o fácil a un valor numérico de coma flotante (n.º de días desde la fecha mínima) para hacer lo siguiente a / con él en expresiones de SQL Server utilizando valores numéricos y operadores:

2.2.1. sumar o restar # de días o días parciales. Nota: Usando DateAdd La función como una solución alternativa no es trivial cuando necesita considerar varias partes, si no todas, de la fecha y hora.

2.2.2. tome la diferencia entre dos fechas para el cálculo de "edad". Nota: no puede simplemente usar SQL Server DateDiff Función en su lugar, porque no computa age como la mayoría de la gente esperaría si los dos tiempos coinciden con un límite de fecha / hora de calendario / reloj de las unidades especificadas, incluso si se trata de una pequeña fracción de esa unidad, devolverá la diferencia como 1 de esa unidad frente a .0. Por ejemplo, el DateDiff en Day's de dos fechas con solo 1 milisegundo de diferencia devolverá 1 frente a 0 (días) si esos tiempos de fecha están en días calendario diferentes (es decir, "1999-12-31 23: 59: 59.9999999" y "2000-01- 01 00: 00: 00.0000000 "). Las mismas fechas de diferencia de 1 milisegundo si se mueven para que no crucen un día calendario, devolverán un "DateDiff" en DayEs de 0 (días).

2.2.3. tomar el Avg de fecha y hora (en una consulta agregada) simplemente convirtiendo a "Float" primero y luego nuevamente a DateTime.

NOTA: Para convertir DateTime2 a un valor numérico, tiene que hacer algo como la siguiente fórmula, que aún asume que sus valores no son inferiores al año 1970 (lo que significa que está perdiendo todo el rango extra más otros 217 años). Nota: es posible que no pueda simplemente ajuste la fórmula para permitir un rango extra ya que puede encontrarse con problemas de desbordamiento numérico.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) / 1.0E + 9) / 86400.0 - Fuente: " https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html "

Por supuesto, también podrías Cast a DateTime primero (y si es necesario, de nuevo a DateTime2), pero perdería los beneficios de precisión y alcance (todos antes del año 1753) de DateTime2 vs. DateTime que son prolly los 2 más grandes y al mismo tiempo prolly los 2 menos necesarios, lo que plantea la pregunta de por qué usarlo cuando se pierden las conversiones implícita / fácil al punto flotante numérico (# de días) para suma / resta / "edad "(vs. DateDiff) / Avg Calcs se beneficia, que es uno grande en mi experiencia.

Por cierto, el Avg de fecha y hora es (o al menos debería ser) un caso de uso importante. a) Además del uso para obtener la duración promedio cuando se usan los tiempos de fecha (desde una fecha base común) para representar la duración (una práctica común), b) también es útil obtener una estadística tipo tablero de instrumentos sobre la fecha promedio. el tiempo está en la columna de fecha y hora de un rango / grupo de Filas. c) Un estándar (o al menos debería ser estándar) La consulta ad-hoc para monitorear / solucionar problemas de valores en una Columna que puede no ser válida nunca / por más tiempo y / o puede que deba ser desaprobada es listar para cada valor el recuento de ocurrencia y (si está disponible) el Min, Avg y Max sellos de fecha y hora asociados con ese valor.


8
2017-07-10 19:00