Por que a data e hora da pesquisa da minha consulta não corresponde?

Como vários outros mencionaram em comentários e outras respostas à sua pergunta, o problema principal 2015-07-27 23:59:59.999está sendo arredondado 2015-07-28 00:00:00.000pelo SQL Server. De acordo com a documentação para DATETIME:

Intervalo de tempo - 00:00:00 até 23:59:59.997

Observe que o intervalo de tempo nunca pode ser .999. Mais abaixo na documentação, ele especifica as regras de arredondamento que o SQL Server usa para o dígito menos significativo.

Observe que o dígito menos significativo pode ter apenas um dos três valores potenciais: "0", "3" ou "7".

Existem várias soluções/soluções alternativas para isso que você pode usar.

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

Das cinco opções que apresentei acima, consideraria as opções 1 e 3 as únicas opções viáveis. Eles transmitem sua intenção com clareza e não serão interrompidos se você atualizar os tipos de dados. Se você estiver usando o SQL Server 2008 ou mais recente, acho que a opção 3 deve ser sua abordagem preferida. Isso é especialmente verdadeiro se você puder deixar de usar o DATETIMEtipo de dados para um DATEtipo de dados para sua posted_datecoluna.

Em relação à opção 3, uma explicação muito boa sobre alguns problemas pode ser encontrada aqui: Cast to date é sargável, mas é uma boa ideia?

Não gosto das opções 2 e 5 porque os .997segundos fracionários serão apenas mais um número mágico que as pessoas vão querer "consertar". Por mais algumas razões pelas quais BETWEENnão é amplamente adotado, você pode querer conferir este post .

Eu não gosto da opção 4 porque converter tipos de dados em uma string para fins de comparação parece sujo para mim. Um motivo mais qualitativo para evitá-lo no SQL Server é que ele afeta a capacidade de sargibilidade , pois você não pode executar uma busca de índice e isso frequentemente resultará em desempenho inferior.

Para obter mais informações sobre a maneira certa e a maneira errada de lidar com consultas de intervalo de datas, confira esta postagem de Aaron Bertrand .

Na parte, você poderia manter sua consulta original e se comportaria conforme desejado se você alterasse sua posted_datecoluna de a DATETIMEpara a DATETIME2(3). Isso economizaria espaço de armazenamento no servidor, proporcionaria maior precisão com a mesma precisão, seria mais compatível/portátil com os padrões e permitiria ajustar facilmente a precisão/exatidão se suas necessidades mudarem no futuro. No entanto, esta é apenas uma opção se você estiver usando o SQL Server 2008 ou mais recente.

Como um pouco de curiosidade, a 1/300precisão de um segundo DATETIMEparece ser um atraso do UNIX por esta resposta do StackOverflow . O Sybase que tem uma herança compartilhada tem uma precisão semelhante 1/300a um segundo em seus tipos DATETIMEdeTIME dados, mas seus dígitos menos significativos são um pouco diferentes em "0", "3" e "6". Na minha opinião, a 1/300precisão de um segundo e/ou 3,33ms é uma decisão arquitetural infeliz, pois o bloco de 4 bytes para o tipo de dados do SQL Server DATETIMEpoderia facilmente suportar uma precisão de 1ms.

Como você está usando o datetimetipo de dados, você precisa entender como o SQL Server arredonda os dados de data e 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 a consulta abaixo, você pode ver facilmente o problema de arredondamento que o sql server faz quando você usa DATETIMEo tipo de dados.

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

Clique para ampliar

DATETIME2existe desde o SQL Server 2008, então comece a usá-lo em vez do DATETIME. Para sua situação, você pode usar datetime2com precisão de 3 decimais , por exemplo datetime2(3).

Benefícios do uso datetime2:

• Suporta até 7 casas decimais para o componente de tempo versus datetimesuporta apenas 3 casas decimais .. e, portanto, você vê o problema de arredondamento, pois por padrão datetimearredonda o mais próximo .003 secondscom incrementos de .000ou .003segundos .007.
• datetime2é muito mais preciso do que datetimee datetime2dá a você o controle de DATEe TIMEem oposição a datetime.

Referência:

• O guia definitivo para os tipos de dados datetime
• Ambiente de trabalho DATA/HORA do SQL Server

Conversão Implícita

Eu suponho que o tipo de dados post_date é Datetime. No entanto, não importa se o tipo do outro lado é Datetime, Datetime2 ou apenas Time porque a string (Varchar) será convertida implicitamente em Datetime.

Com data_postada declarada como Datetime2 (ou Time), a posted_date <= '2015-07-27 23:59:59.99999'cláusula where falha porque embora 23:59:59.99999seja um valor Datetime2 válido, este não é um valor Datetime válido:

 Conversion failed when converting date and/or time from character string.

Intervalo de tempo para Datetime

O intervalo de tempo de Datetime é de 00:00:00 a 23:59:59.997. Portanto, 23:59:59.999 está fora do intervalo e deve ser arredondado para cima ou para baixo para o valor mais próximo.

Precisão

Além disso, os valores de data e hora são arredondados por incrementos de 0,000, 0,003 ou 0,007 segundos. (ou seja, 000, 003, 007, 010, 013, 017, 020, ..., 997)

Este não é o caso com o valor 2015-07-27 23:59:59.999que está dentro deste intervalo: 2015-07-27 23:59:59.997e 2015-07-28 0:00:00.000.

Esse intervalo corresponde às opções anteriores e posteriores mais próximas, ambas terminando com .000, .003 ou .007.

Arredondando para cima ou para baixo ?

Como está mais próximo de 2015-07-28 0:00:00.000(+1 versus -2) do que 2015-07-27 23:59:59.997, a string é arredondada para cima e se torna este valor Datetime: 2015-07-28 0:00:00.000.

Com um limite superior como 2015-07-27 23:59:59.998(ou 0,995, 0,996, 0,997, 0,998), ele teria sido arredondado para baixo 2015-07-27 23:59:59.997e sua consulta teria funcionado conforme o esperado. No entanto, não teria sido uma solução, mas apenas um valor de sorte.

Tipos Datetime2 ou Time

Os intervalos de tempo Datetime2 e Time terminam 00:00:00.0000000com 23:59:59.9999999uma precisão de 100 ns (o último dígito quando usado com uma precisão de 7 dígitos).

No entanto, um intervalo Datetime(3) não é semelhante ao intervalo Datetime:

• Data e hora 0:0:00.000para23:59:59.997
• Datahora2 0:0:00.000000000para23:59:59.999

Solução

No final, é mais seguro procurar datas abaixo do dia seguinte do que datas abaixo ou iguais ao que você acha que é o último fragmento de hora do dia. Isso ocorre principalmente porque você sabe que o dia seguinte sempre começa às 0:00:00.000, mas diferentes tipos de dados podem não ter o mesmo horário no final do dia:

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.000lhe dará resultados precisos e é a melhor opção
• <= 2015-07-27 23:59:59.xxxpode retornar valores inesperados quando não for arredondado para o que você acha que deveria ser.
• A conversão para data e o uso da função devem ser evitados porque limitam o uso de índices

Poderíamos pensar que alterar [posted_date] para Datetime2 e sua maior precisão poderia corrigir esse problema, mas não ajudará porque a string ainda é convertida em Datetime. No entanto, se um elenco for adicionado cast(2015-07-27 23:59:59.999' as datetime2), isso funcionará bem

Transmitir e converter

O Cast pode converter um valor com até 3 dígitos em Datetime ou com até 9 dígitos em Datetime2 ou Time e arredondar para a precisão correta.

Deve-se notar que Cast of Datetime2 e Time2 pode dar resultados diferentes:

• select cast('20150101 23:59:59.999999999' as datetime2(7))é arredondado para cima 2015-05-03 00:00:00.0000000 (para valor maior que 999999949)
• select cast('23:59:59.999999999' as time(7))=> 23:59:59.9999999

Ele meio que corrige o problema que o datetime está tendo com os incrementos 0, 3 e 7, embora ainda seja sempre melhor procurar datas antes do 1º nano segundo do dia seguinte (sempre 0:00:00.000).

MSDN de origem: datetime (Transact-SQL)

está arredondando

 select cast('2015-07-27 23:59:59.999' as datetime) 
 returns 2015-07-28 00:00:00.000

.998, .997, .996, .995 todos lançados / redondos para .997

Deveria usar

select * 
from A 
where posted_date >= '2015-07-27 00:00:00.000' 
  and posted_date <  '2015-07-28 00:00:00.000'

ou

where cast(posted_date as date) = '2015-07-27'

Veja a precisão neste link
Sempre relatado como 0,000, 0,003, 0,007