SQL select date range返回范围外的日期

Question

选择基于日期的记录的负载，但我得到的日期返回的范围内没有任何想法?

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

例如，这将返回一个日期为"2008-04-10 00:00:00.000"的记录

Answer 1

不使用参数时，始终使用不变的datetime格式:

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

Answer 2

最好使用SQL between子句检查范围。

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

Answer 3

还可以通过名称或缩写指定月份，如下所示:

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

或者您可以使用BETWEEN子句来简化查询:

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

Answer 4

试试这个

这必须给出预期的结果

 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'))