SQL Server datetime filter query

Question

I have this query in SQL Server:

SELECT 
    [Order Date], SUM([Profit]) 
FROM
    [sample].[dbo].[superstore]
WHERE  
    [Order Date] BETWEEN '2012-06-21 00:00:00.000' AND '2012-09-21 23:59:59.999'
GROUP BY 
    [Order Date]
ORDER BY  
    [Order Date];

result of this query has record with [Order Date] of 2012-06-21 00:00:00.000

but with this query

SELECT 
    [Order Date], SUM([Profit]) 
FROM
    [sample].[dbo].[superstore]
WHERE  
    [Order Date] BETWEEN '2012-06-21 00:00:00.000' AND '2012-09-21 23:59:59.998'
GROUP BY 
    [Order Date]
ORDER BY 
    [Order Date];

I don't get this record.

Does SQL Server have any properties that is missing here?

Hint: type of [Order Date] is datetime.

question from:https://stackoverflow.com/questions/65952835/how-to-get-the-data-of-specific-date-when-the-datetime-is-stored-in-string

Answer 1

SQL Server date/time columns have precisions that vary depending on the exact type. In your first, the "0.999" is being rounded up.

The safest approach is to eschew between and the time components, with something like:

SELECT [Order Date] , sum([Profit]) 
FROM [sample].[dbo].[superstore]
WHERE [Order Date] >=  '2012-06-21' AND
      [Order Date] < '2012-09-22'
GROUP BY [Order Date]
ORDER BY [Order Date];