CODEWITHSUNDEEP
sql-serversql-server-2012
JTR
JTR

Reputation: 333

How to Select all data where the condition are between the same date?

I have stored procedure with parameters @firstDate and @lastDate I'm also have a table named Products with fields id, productName, status and orderDate.

I want to get the data from orderDate with BETWEEN condition.
I've tried this syntax :

CREATE PROCEDURE sp_Order
(
  @firstDate    DATETIME,
  @lastDate     DATETIME
)
AS
BEGIN
  SELECT
     productName,
     orderDate
  FROM
     Products
  WHERE
     orderDate BETWEEN @firstDate AND @lastDate
     AND status = 'Active'
  GROUP BY
     productName,
     orderDate
  ORDER BY
     orderDate
END

This is the sample data that I've inserted into my table

enter image description here

When I'm add the same date, e.g '2015-10-12' for @firtDate and @lastDate it's shows nothing, and when I'm add '2015-10-10' for @firstDate and '2015-10-12' for @lastDate it's only shows list of products from date '2015-10-10'.

How could this happen? And what's the right syntax for this?

Thanks for answers.

Upvotes: 0

Views: 123

Answers (1)

Julien Vavasseur
Julien Vavasseur

Reputation: 3952

When you are looking for dates on a given day using datetime or datetime2 datatype, the search condition should be between the 1st nanosecond of the day and the 1st of the next day.

This is the best option in order to avoid lost of accuracy or dates being round up or down.

It is also best to use the DATE data type if you don't need the time part. You can always cast it back to datetime.

If I follow your example and look for 2015-10-12, you are doing:

orderDate between '2015-10-12 00:00:00.000' AND '2015-10-12 00:00:00.000'
orderDate >= '2015-10-12 00:00:00.000' AND orderDate <= '2015-10-12 00:00:00.000'

When it should use:

set @firstDate = '2015-10-12 00:00:00.000' set @lastDate = '2015-10-13 00:00:00.000'

As it is explain on the BETWEEN page:

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

The BETWEEN function is similar to:

orderDate BETWEEN @firstDate AND @lastDate
orderDate >= @firstDate AND orderDate <= @lastDate

You will end up with value for '2015-10-12 00:00:00.000' as well.

It also explain what to do in the Remarks section:

To specify an exclusive range, use the greater than (>) and less than operators (<).

You therefore have to use:

orderDate >= @firstDate AND orderDate < @lastDate

Since you want days between @firstDate and @lastDate and including these values, the best option is

orderDate >= @firstDate AND orderDate < DATEADD(day, 1, @lastDate)

Still with @firstDate and @lastDate equal to 2015-10-12, the query will search for:

orderDate >= `2015-10-12` AND orderDate < DATEADD(day, 1, `2015-10-12`)
orderDate >= '2015-10-12 00:00:00.000' AND orderDate < '2015-10-13 00:00:00.000'

Finally, you will also find a lot of good thing in the answers of this question: Why does my query search datetime not match?

Upvotes: 2

Related Questions