Solución:
Como varios otros han mencionado en los comentarios y otras respuestas a su pregunta, el problema central es 2015-07-27 23:59:59.999 se redondea a 2015-07-28 00:00:00.000 por SQL Server. Según la documentación para FECHA Y HORA:
Intervalo de tiempo: de 00:00:00 a 23: 59: 59.997
Tenga en cuenta que el intervalo de tiempo puede Nunca ser .999. Más abajo en la documentación, especifica las reglas de redondeo que usa SQL Server para el dígito menos significativo.
Observe que el dígito menos significativo solo puede tener uno de tres valores potenciales: “0”, “3” o “7”.
Hay varias soluciones / métodos alternativos para esto que puede utilizar.
-- Option 1
SELECT
*
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
AND posted_date < '2015-07-28 00:00:00.000' --Round up and remove equality
-- Option 2
SELECT
*
FROM A
WHERE posted_date >= '2015-07-27 00:00:00.000'
AND posted_date <= '2015-07-27 23:59:59.997' --Round down and keep equality
-- Option 3
SELECT
*
FROM A
WHERE CAST(posted_date AS DATE) = '2015-07-27' -- Use different data type
-- Option 4
SELECT
*
FROM A
WHERE CONVERT(CHAR(8), DateColumn, 112) = '20150727' -- Cast to string stripping off time
-- Option 5
SELECT
*
FROM A
WHERE posted_date BETWEEN '2015-07-27 00:00:00.000'
AND '2015-07-27 23:59:59.997' --Use between
De las cinco opciones que he presentado anteriormente, consideraría las opciones 1 y 3 como las únicas opciones viables. Transmiten su intención con claridad y no se romperán si actualiza los tipos de datos. Si está utilizando SQL Server 2008 o más reciente, creo que la opción 3 debería ser su enfoque preferido. Eso es especialmente cierto si puede dejar de usar el FECHA Y HORA tipo de datos a un FECHA tipo de datos para su posted_date columna.
Con respecto a la opción 3, aquí se puede encontrar una muy buena explicación sobre algunos problemas: Cast hasta la fecha se puede sargalar, pero ¿es una buena idea?
No me gustan las opciones 2 y 5 porque el .997 fracciones de segundo va a ser sólo otro número mágico que la gente querrá “arreglar”. Por algunas razones más por las que BETWEEN no está ampliamente aceptado, es posible que desee consultar esta publicación.
No me gusta la opción 4 porque convertir tipos de datos en una cadena para fines de comparación me parece sucio. Una razón más cualitativa para evitarlo en SQL Server es su impacto sargabilidad también conocido como no puede realizar una búsqueda de índice y eso con frecuencia dará como resultado un rendimiento deficiente.
Para obtener más información sobre la forma correcta e incorrecta de manejar consultas de rango de fechas, consulte esta publicación de Aaron Bertrand.
Al partir, podrá mantener su consulta original y se comportará como desee si cambia su posted_date columna de un FECHA Y HORA a un DATETIME2(3). Eso ahorraría espacio de almacenamiento en el servidor, le brindaría una mayor precisión con la misma precisión, sería más portátil / compatible con los estándares y le permitiría ajustar fácilmente la precisión si sus necesidades cambian en el futuro. Sin embargo, esta es solo una opción si está utilizando SQL Server 2008 o una versión posterior.
Como un poco de trivia 1/300 de una segunda precisión con FECHA Y HORA parece ser una retención de UNIX según esta respuesta de StackOverflow. Sybase, que tiene una herencia compartida, tiene un 1/300 de una segunda precisión en su DATETIME y TIME tipos de datos, pero sus dígitos menos significativos son un poco diferentes en “0”, “3” y “6”. En mi opinion el 1/300 de un segundo y / o 3.33ms de precisión es una decisión arquitectónica desafortunada ya que el bloque de 4 bytes por el momento en SQL Server FECHA Y HORA El tipo de datos podría haber soportado fácilmente una precisión de 1 ms.
Ya que estas usando fecha y hora tipo de datos, debe comprender cómo el servidor SQL redondea los datos de fecha y hora.
╔═══════════╦═════╦═════════════════════════════╦═════════════════════════════╦══════════╦═══════════╗
║ Name ║ sn ║ Minimum value ║ Maximum value ║ Accuracy ║ Storage ║
╠═══════════╬═════╬═════════════════════════════╬═════════════════════════════╬══════════╬═══════════╣
║ datetime ║ dt ║ 1753-01-01 00:00:00.000 ║ 9999-12-31 23:59:59.997 ║ 3.33 ms ║ 8 bytes ║
║ datetime2 ║ dt2 ║ 0001-01-01 00:00:00.0000000 ║ 9999-12-31 23:59:59.9999999 ║ 100ns ║ 6-8 bytes ║
╚═══════════╩═════╩═════════════════════════════╩═════════════════════════════╩══════════╩═══════════╝
Usando la siguiente consulta, puede ver fácilmente el problema de redondeo que hace el servidor SQL cuando usa DATETIME tipo de datos.
select '2015-07-27 00:00:00.000' as Original_startDateTime,
convert(datetime ,'2015-07-27 00:00:00.000') as startDateTime,
'2015-07-27 23:59:59.999' as Original_endDateTime,
convert(datetime ,'2015-07-27 23:59:59.999') as endDateTime,
'2015-07-27 00:00:00.000' as Original_startDateTime2,
convert(datetime2 ,'2015-07-27 00:00:00.000') as startDateTime2, -- default precision is 7
'2015-07-27 23:59:59.999' as Original_endDateTime2,
convert(datetime2 ,'2015-07-27 23:59:59.999') as endDateTime2 -- default precision is 7
Click para agrandar
DATETIME2 ha existido desde SQL Server 2008, así que comience a usarlo en lugar de DATETIME. Para su situación, puede utilizar datetime2 con precisión de 3 decimales p.ej datetime2(3).
Beneficios de usar datetime2:
- Admite hasta 7 lugares decimales para el componente de tiempo vs.
datetimeadmite solo 3 lugares decimales … y, por lo tanto, ve el problema del redondeo, ya que de forma predeterminadadatetimeredondea el más cercano.003 secondscon incrementos de.000,.003o.007segundos. -
datetime2es mucho más preciso quedatetimeydatetime2te da el control deDATEyTIMEOpuesto adatetime.
Referencia:
- La guía definitiva para los tipos de datos de fecha y hora
- Banco de trabajo FECHA / HORA de SQL Server
Conversión implícita
Supuse que el tipo de datos posted_date es Datetime. Sin embargo, no importa si el tipo en el otro lado es Datetime, Datetime2 o simplemente Time porque la cadena (Varchar) se convertirá implícitamente a Datetime.
Con posted_date declarado como Datetime2 (o Time), el posted_date <= '2015-07-27 23:59:59.99999' donde la cláusula falla porque aunque 23:59:59.99999 es un valor de Datetime2 válido, este no es un valor de Datetime válido:
Conversion failed when converting date and/or time from character string.
Rango de tiempo para fecha y hora
El intervalo de tiempo de Datetime es de 00:00:00 a 23: 59: 59.997. Por lo tanto, 23: 59: 59.999 está fuera de rango y debe redondearse hacia arriba o hacia abajo al valor más cercano.
Precisión
Además, los valores de fecha y hora se redondean en incrementos de .000, .003 o .007 segundos. (es decir, 000, 003, 007, 010, 013, 017, 020, …, 997)
Este no es el caso del valor 2015-07-27 23:59:59.999 que está dentro de este rango: 2015-07-27 23:59:59.997 y 2015-07-28 0:00:00.000.
Este rango corresponde a las opciones anteriores y siguientes más cercanas, y ambas terminan en .000, .003 o .007.
Redondeo hacia arriba o hacia abajo?
Porque esta mas cerca de 2015-07-28 0:00:00.000 (+1 contra -2) que 2015-07-27 23:59:59.997, la cadena se redondea hacia arriba y se convierte en este valor de fecha y hora: 2015-07-28 0:00:00.000.
Con un límite superior como 2015-07-27 23:59:59.998 (o .995, .996, .997, .998), habría sido redondeado a 2015-07-27 23:59:59.997 y su consulta habría funcionado como se esperaba. Sin embargo, no habría sido una solución, sino un valor afortunado.
Tipos de fecha y hora2 u hora
Los rangos de tiempo Datetime2 y Time son 00:00:00.0000000 mediante 23:59:59.9999999 con una precisión de 100ns (el último dígito cuando se usa con una precisión de 7 dígitos).
Sin embargo, un rango de fecha y hora (3) no es similar al rango de fecha y hora:
- Fecha y hora
0:0:00.000para23:59:59.997 - Fecha y hora2
0:0:00.000000000para23:59:59.999
Solución
Al final, es más seguro buscar fechas debajo del día siguiente que fechas debajo o iguales a lo que crees que es el último fragmento de hora del día. Esto se debe principalmente a que sabe que el día siguiente siempre comienza a las 0: 00: 00.000 pero es posible que diferentes tipos de datos no tengan la misma hora al final del día:
Datetime `0:0:00.000` to `23:59:59.997`
Datetime2 `0:0:00.000000000` to `23:59:59.999-999-900`
Time2 `0:0:00.000000000` to `23:59:59.999-999-900`
-
< 2015-07-28 0:00:00.000te dará un preciso resultados y es la mejor opción -
<= 2015-07-27 23:59:59.xxxpuede devolver valores inesperados cuando no se redondea a lo que cree que debería ser. - Se debe evitar la conversión a la fecha y el uso de la función porque limita el uso de índices
Podríamos pensar que cambiando [posted_date] a Datetime2 y su mayor precisión podría solucionar este problema, pero no ayudará porque la cadena todavía se convierte a Datetime. Sin embargo, si se agrega un yeso cast(2015-07-27 23:59:59.999' as datetime2), esto funciona bien
Transmitir y convertir
Cast puede convertir un valor de hasta 3 dígitos en Datetime o con hasta 9 dígitos en Datetime2 u Time y redondearlo con la precisión correcta.
Cabe señalar que Cast of Datetime2 y Time2 pueden dar resultados diferentes:
-
select cast('20150101 23:59:59.999999999' as datetime2(7))se redondea 2015-05-03 00: 00: 00.0000000 (para un valor superior a 999999949) -
select cast('23:59:59.999999999' as time(7))=> 23: 59: 59.9999999
De alguna manera soluciona el problema que tiene datetime con los incrementos 0, 3 y 7, aunque siempre es mejor buscar fechas antes del primer nano segundo del día siguiente (siempre 0: 00: 00.000).
Fuente MSDN: datetime (Transact-SQL)