CODEWITHSUNDEEP
sqlsql-serverdatetime
Babak
Babak

Reputation: 198

SQL Server datetime filter query

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.

Upvotes: 1

Views: 66893

Answers (3)

Jason A. Long
Jason A. Long

Reputation: 4442

Using the BETWEEN operator for date ranges seems appealing at first but, as you're finding out, it's a headache. If you stick to the following format, you'll find that it works no matter the date/time data type.

Note: Where BETWEEN is a logical >= and <=, we're shifting the "end date, to midnight of the following day. 

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

Upvotes: 2

satputenandkumar
satputenandkumar

Reputation: 67

Use Below Query

 SELECT
  CAST([Order Date] AS date),
  SUM([Profit])
FROM [sample].[dbo].[superstore]
WHERE CAST([Order Date] AS date) BETWEEN '2012-06-21' AND '2012-09-21'
GROUP BY CAST([Order Date] AS date)
ORDER BY CAST([Order Date] AS date);

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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

Upvotes: 5

Related Questions