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.CustomerFirstName) + ' ' + LTRIM(C.CustomerLastName) as CustomerFullName,
       ROW_NUMBER() OVER(ORDER BY CP.ActionDate) AS RowNumber,
       C.CustomerFirstName,
       C.CustomerLastName,
       C.CustomerCompany,
       C.CustomerPosition,
       C.CustomerCountry,
       C.CustomerProvince,
       C.CustomerContact,
       CP.ActionDate,
       CP.ProductCode,
       CP.CustomerEmail
FROM   tblCustomers C
       JOIN tblCustomerProducts CP
            ON  C.CustomerEmail = CP.CustomerEmail


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

回答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.

回答2:

The smaller date has to be listed first

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

回答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'
ORDER BY 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'
ORDER BY 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

回答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'