Select Query Between Two Dates

I am trying tomake a select join between two specific dates in the database This I feelis on the right path but is incorrect How can I get this to work SELECT --RTRIM(C.CustomerFir

Solution 1:

Instead of

WHERE CP.ActionDate BETWEEN'1/17/2013'AND'19/12/2012`

Try this:

WHERE CP.ActionDate BETWEEN'19/12/2012'AND'1/17/2013'

Note that: This is because the BETWEEN predicate in SQL Server is Asymmetric, this means that value1 BETWEEN value2 AND value3 is the same as Value1 >= Value2 AND Value1 <= Value3, so the value value2 that before the AND must be less than or equal to the value3.

Solution 2:

The smaller date has to be listed first

BETWEEN'12/19/2012'AND'1/17/2013'

Solution 3:

There are a few issues with your current query.

First, you have the dates in the wrong order.

Second, you have the ORDER BY in the wrong place. The ORDER BY is the last item listed in your select:

FROM tblCustomers C
JOIN tblCustomerProducts CP 
   ON C.CustomerEmail = CP.CustomerEmail
WHERE CP.ActionDate BETWEEN'12/19/2012'AND'1/17/2013'ORDERBY ActionDate DESC

You can also use:

FROM tblCustomers C
JOIN tblCustomerProducts CP 
   ON C.CustomerEmail = CP.CustomerEmail
WHERE CP.ActionDate >='12/19/2012'AND CP.ActionDate <='1/17/2013'ORDERBY ActionDate DESC

I would also advise that you be sure that the dates are formatted in the same manner. You have one as DD/MM/YYYY and the other as MM/DD/YYYY

Solution 4:

You misplaced dates, it should be smaller one at first place and larger one at second place:

WHERE CP.ActionDate BETWEEN'19/12/2012'AND'1/17/2013'

OR

WHERE CP.ActionDate >='19/12/2012'AND CP.ActionDate <='1/17/2013'