uso insertar example ejemplo datos como sql sql-server tsql datetime datetime2

insertar - DateTime2 vs DateTime en SQL Server



sql datetime format (14)

Cúal:

¿Es la forma recomendada de almacenar la fecha y la hora en SQL Server 2008+?

Soy consciente de las diferencias en la precisión (y probablemente en el espacio de almacenamiento), pero por el momento las ignoro, ¿existe un documento de mejores prácticas sobre cuándo usar qué, o tal vez deberíamos usar datetime2 solamente?

Acabo de encontrar una ventaja más para DATETIME2 : evita un error en el módulo de adodbapi Python, que explota si se pasa un valor de datetime y datetime biblioteca estándar que tiene microsegundos distintos a cero para una columna DATETIME pero funciona bien si la columna se define como DATETIME2 .

Casi todas las respuestas y comentarios han sido pesados ​​sobre los profesionales y ligeros sobre los contras. Aquí hay un resumen de todos los Pros y Contras hasta ahora, más algunos Contras cruciales (en el # 2 a continuación) que solo he visto mencionados una vez o no.

  1. PROS:

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

1.2. Más rango (1/1/0001 a 12/31/9999 vs. 1/1 / 1753-12 / 31/9999) (aunque el rango adicional, todo antes del año 1753, probablemente no se usará excepto por ejemplo, En aplicaciones históricas, astronómicas, geológicas, etc.

1.3. Coincide exactamente con el rango del rango del Tipo de fecha y DateTime de .NET (aunque ambos se convierten de un lado a otro sin una codificación especial si los valores se encuentran dentro del rango y la precisión del tipo de destino, excepto en el Con # 2.1 que se muestra a continuación; se producirá un error / redondeo).

1.4. Más precisión (100 nanosegundos, también conocido como 0,000,000,1 segundos, frente a 3,33 milisegundos, conocido como 0,003,33 segundos) (aunque es probable que no se use la precisión adicional, excepto en las aplicaciones de ingeniería / científicas).

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

  1. CONTRAS:

2.1. Al pasar un Parámetro a un .NET SqlCommand , debe especificar System.Data.SqlDbType.DateTime2 si puede pasar un valor fuera del rango y / o la precisión de SQL Server DateTime , porque su valor predeterminado es System.Data.SqlDbType.DateTime .

2.2. No se puede convertir de manera implícita / fácil a un valor numérico de punto flotante (# de días desde la fecha y hora mínimos) para hacer lo siguiente a / con él en expresiones de SQL Server usando valores numéricos y operadores:

2.2.1. sumar o restar # de días o días parciales. Nota: el uso de la función DateAdd como solución alternativa no es trivial cuando necesita considerar múltiples, si no todas, las partes de la fecha y la hora.

2.2.2. tome la diferencia entre dos fechas y fechas para propósitos de cálculo de "edad". Nota: en su lugar, no puede simplemente usar la función DateDiff SQL Server, ya que no computa la age como la mayoría de la gente esperaría, ya que si las dos fechas-fecha cruzan un límite de fecha / hora de calendario / reloj de las unidades especificadas, incluso para un pequeña fracción de esa unidad, devolverá la diferencia como 1 de esa unidad frente a 0. Por ejemplo, el DateDiff en Day de dos fechas-fecha solo 1 milisegundo de diferencia devolverá 1 vs. 0 (días) si esos la fecha y la hora aparecen en días calendario diferentes (es decir, "1999-12-31 23: 59: 59.9999999" y "2000-01-01 00: 00: 00.0000000"). La misma diferencia de 1 milisegundo en las fechas y fechas si se mueve para que no se crucen en un día calendario, devolverá un "DateDiff" en Day de 0 (días).

2.2.3. tome el Avg de fechas y horas (en una Consulta Agregada) simplemente convirtiendo primero a "Flotante" y luego nuevamente a DateTime .

NOTA: para convertir DateTime2 en un número, debe hacer algo como la siguiente fórmula que aún asume que sus valores no son menores que el año 1970 (lo que significa que está perdiendo todo el rango adicional más otros 217 años. Nota: Usted es posible que no pueda simplemente ajustar la fórmula para permitir un rango adicional porque puede tener 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ía Cast a DateTime primero (y, si es necesario, de nuevo a DateTime2 ), pero perdería los beneficios de precisión y rango (todos anteriores al año 1753) de DateTime2 comparación con DateTime que son prolly los 2 más grandes y también al mismo tiempo, probablemente los 2 menos necesarios, lo que plantea la pregunta de por qué usarlo cuando se pierden las conversiones implícitas / fáciles a números de punto flotante (número de días) para la suma / resta / "edad" (vs. DateDiff ) / Avg El beneficio de calcs que es grande en mi experiencia.

Por cierto, el Avg de fechas y fechas es (o al menos debería ser) un caso de uso importante. a) Además de su uso para obtener la duración promedio cuando se usan las fechas y horas (ya que se utiliza una fecha y hora base común) para representar la duración (una práctica común), b) también es útil para obtener una estadística de tipo tablero de control sobre cuál es la fecha promedio la hora está en la columna fecha-hora de un rango / grupo de filas. c) Una consulta ad-hoc estándar (o al menos debería ser estándar) para monitorear / resolver problemas en una columna que puede que ya no sea válida por más tiempo y / o que deba ser desaprobada es para enumerar para cada valor el recuento de ocurrencias y (si está disponible) las Min fecha y hora Min , Avg y máximas asociadas con ese valor.

Coincido con @marc_s y @Adam_Poward - DateTime2 es el método preferido para avanzar. Tiene un rango de fechas más amplio, mayor precisión y utiliza almacenamiento igual o menor (según la precisión).

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

La versión de .NET de Datetime tiene un rango y precisión similares a los de DateTime2 . Cuando se asigna un .net Datetime al antiguo SQL DateTime produce un redondeo implícito . El antiguo DateTime SQL tiene una precisión de 3 milisegundos. Esto significa que 11:59:59.997 es lo más cerca que puede llegar al final del día. Cualquier cosa superior 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:

Creo que DATETIME2 es la mejor manera de almacenar la fecha, porque tiene más eficiencia que DATETIME. En SQL Server 2008 puede usar DATETIME2, almacena una fecha y una hora, toma 6-8 bytes para almacenar y tiene una precisión de 100 nanosegundos. Así que cualquiera que necesite una mayor precisión de tiempo querrá DATETIME2.

DateTime2 causará estragos si usted es un desarrollador de Access que intenta escribir Ahora () en el campo en cuestión. Acabo de realizar una migración de Access -> SQL 2008 R2 y puso todos los campos de fecha y hora en DateTime2. Anexando un registro con Ahora () 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.

Este es 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

Entonces, si quiero almacenar información hasta el segundo, pero no en milisegundos, puedo guardar 2 bytes cada uno si uso datetime2 (0) en lugar de datetime o datetime2 (7).

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

Use los tipos de datos de datetimeoffset , date , datetime2 y datetimeoffset para el nuevo trabajo. Estos tipos se alinean con el estándar de SQL. Son más portátiles. time , datetime2 y datetimeoffset proporcionan una precisión de más segundos. datetimeoffset proporciona soporte de zona horaria para aplicaciones implementadas globalmente.

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

La interpretación de las cadenas de fecha en datetime y datetime2 puede ser diferente cuando se utilizan configuraciones DATEFORMAT no son de EE. DATEFORMAT . P.ej

set dateformat dmy declare @d datetime, @d2 datetime2 select @d = ''2013-06-05'', @d2 = ''2013-06-05'' select @d, @d2

Esto devuelve 2013-05-06 (es decir, 6 de mayo) para datetime y 2013-06-05 (es decir, 5 de junio) para datetime2 . Sin embargo, con dateformat configurado en mdy , tanto @d como @d2 regresan 2013-06-05 .

El comportamiento de datetime parece estar en desacuerdo con la documentación de MSDN de SET DATEFORMAT que dice: Algunos formatos de cadenas de caracteres, por ejemplo, ISO 8601, se interpretan independientemente de la configuración de DATEFORMAT . ¡Obviamente no es cierto!

Hasta que me mordió esto, siempre pensé que las fechas de yyyy-mm-dd solo se manejarían correctamente, independientemente de la configuración de idioma / configuración regional.

Pregunta antigua ... Pero quiero agregar algo que no haya sido mencionado por nadie aquí ... (Nota: Esta es mi propia observación, así que no pida ninguna referencia)

Datetime2 es más rápido cuando se usa en criterios de filtro.

TLDR:

En SQL 2016 tenía una tabla con cientos de miles de filas y una columna de fecha y hora ENTRY_TIME porque era necesario almacenar el tiempo exacto hasta segundos. Al ejecutar una consulta compleja con muchas combinaciones y una consulta secundaria, cuando usé la cláusula where como:

WHERE ENTRY_TIME >= ''2017-01-01 00:00:00'' AND ENTRY_TIME < ''2018-01-01 00:00:00''

La consulta estuvo bien inicialmente cuando había cientos de filas, pero cuando aumentó el número de filas, la consulta comenzó a dar este error:

Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Quité la cláusula where, e inesperadamente, la consulta se ejecutó en 1 segundo, aunque ahora se recuperaron TODAS las filas para todas las fechas. Ejecuto la consulta interna con la cláusula where, y tomó 85 segundos, y sin la cláusula where tomó 0.01 segundos.

Me encontré con muchos subprocesos aquí para este problema como rendimiento de filtrado de fecha y hora

Optimicé la consulta un poco. Pero la velocidad real que obtuve fue cambiando la columna datetime a datetime2.

Ahora, la misma consulta que se agotó antes lleva menos de un segundo.

aclamaciones

Según este artículo , si desea tener la misma precisión de DateTime con DateTime2, simplemente tiene que usar DateTime2 (3). Esto debería darle la misma precisión, ocupar menos bytes y proporcionar un rango expandido.

Si bien hay una mayor precisión con datetime2 , algunos clientes no admiten la fecha , la hora o datetime2 y lo obligan a convertir a una cadena literal. Específicamente, Microsoft menciona problemas de ODBC, OLE DB, JDBC y SqlClient de "nivel inferior" con estos tipos de datos y tiene un datetime2 muestra cómo cada uno puede asignar el tipo.

Si el valor es compatible con la precisión, use datetime

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

Además, si lo necesita, DATETIME2 puede ser más preciso en términos de tiempo; DATETIME se limita a 3 1/3 milisegundos, mientras que DATETIME2 puede tener una precisión de hasta 100 ns.

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

Si tiene la opción, recomendaría usar DATETIME2 siempre que sea posible. No veo ningún beneficio al usar DATETIME (a excepción de la compatibilidad con versiones anteriores). Tendrá menos problemas (con fechas fuera de rango y problemas como ese).

Además: si solo necesita la fecha (sin parte de la hora), use DATE: es tan bueno como DATETIME2 y también le ahorra espacio. :-) Lo mismo ocurre con el tiempo solamente - use TIME . ¡Para eso están estos tipos!

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

  1. mayor rango de valores
  2. mejor exactitud
  3. menor espacio de almacenamiento (si se especifica la precisión opcional especificada por el usuario)

tenga en cuenta los siguientes puntos

  • Sintaxis
    • datetime2 [(precisión fraccional de segundos => Mirar debajo del tamaño de almacenamiento)]
  • Escala de precisión
    • De 0 a 7 dígitos, con una precisión de 100ns.
    • La precisión por defecto es de 7 dígitos.
  • Tamaño de almacenamiento
    • 6 bytes para precisión inferior a 3;
    • 7 bytes para precisión 3 y 4.
    • Toda otra precisión requiere 8 bytes .
  • DateTime2 (3) tiene el mismo número de dígitos que DateTime pero utiliza 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 la imagen: MCTS Self-Paced Training Kit (Examen 70-432): Microsoft® SQL Server® 2008 - Implementación y mantenimiento Capítulo 3: Tablas -> Lección 1: Crear tablas -> página 66

Select ValidUntil + 1 from Documents

El SQL anterior no funcionará con un campo DateTime2. Devuelve y el error "Operand type clash: datetime2 es incompatible con int"

Agregar 1 para obtener al día siguiente es algo que los desarrolladores han estado haciendo con las fechas durante años. Ahora Microsoft tiene un nuevo campo datetime2 que no puede manejar esta funcionalidad simple.

"Usemos este nuevo tipo que es peor que el anterior", ¡no lo creo!