CODEWITHSUNDEEP
sqlsql-server
D-W
D-W

Reputation: 5341

sql select date range returns date outside range

Selecting aload of records based on dates, but im getting dates returned which are not inside the range any ideas?

SELECT *
FROM TABLE
WHERE (
        PAYMENT_DATE >= CONVERT(DATETIME, '2010-05-06')
        AND PAYMENT_DATE <= CONVERT(DATETIME, '2013-11-11')
        )

This for example will return a record that has a date of "2008-04-10 00:00:00.000"

Upvotes: 0

Views: 3658

Answers (4)

Luaan
Luaan

Reputation: 63752

When you're not using parameters, always use the invariant datetime format:

 SELECT *
   FROM [Table]
  WHERE ([PaymentDate] >= '20100506' AND [PaymentDate] <= '20131111')

Upvotes: 2

Ishtiaq
Ishtiaq

Reputation: 1058

Its good to check the range with SQL between Clause. 

 SELECT *
   FROM table
  WHERE PAYMENT_DATE BETWEEN CONVERT(datetime, '2010-05-06')
                         AND CONVERT(datetime, '2013-11-11')

Upvotes: 1

Mohit
Mohit

Reputation: 11314

Just give a try to this

this must give the expected result

 SELECT *
   FROM table
  WHERE (Convert(datetime,PAYMENT_DATE,103) >= CONVERT(datetime, '2010-05-06') 
    AND Convert(datetime,PAYMENT_DATE,103) <= CONVERT(datetime, '2013-11-11'))

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20499

Also, you can specify the month by its name or abbreviation, like below:

SELECT *
FROM TABLE
WHERE (
            PAYMENT_DATE >= CONVERT(DATETIME, '2010-May-06')
        AND PAYMENT_DATE <= CONVERT(DATETIME, '2013-Nov-11')
        )

Or you can just use the BETWEEN clause to simplify the query:

SELECT *
FROM TABLE
WHERE 
    PAYMENT_DATE BETWEEN '2010-May-06' AND '2013-Nov-11'

Upvotes: 0

Related Questions