locked
find data between 2 dates RRS feed

  • Question

  • hi
     
    i have in my access datetime filed that hold date in short format in this format: `01/08/2011`
     
    i have in my C# program datetimePicker that hold date in this format:
     
    `dt_from_A.Value.ToShortDateString()` and hold `01/08/2011`
     
    i try to run this query :
     
    SELECT * FROM ReturnConfTbl where Tdate >= #01/08/2011# and Tdate <= #01/08/2011#
     
    but i dont receive any records
    Monday, August 1, 2011 12:25 PM

Answers

  • So, you are looking for records on January 8, 2011.  Access interprets literal dates ## as mm.dd.yyyy format and not dd/mm/yyyy format.  The best way to handle this is to always use yyyy-mm-dd format when you are using literal dates.

    Take a look at this article on International Dates in Access at:
       http://allenbrowne.com/ser-36.html


    John Spencer Access MVP 2002-2005, 2007-2011
    • Marked as answer by Macy Dong Tuesday, August 9, 2011 7:48 AM
    Monday, August 1, 2011 7:58 PM
  • Also note that if there is a time in addition to the date, you should rather use, in order to get anything for the first of August 2011 (note that # assumes that you are using the US format, by default) :

     

    SELECT * FROM ReturnConfTbl where Tdate >= #08/01/2011# and Tdate < #08/02/2011#

    • Marked as answer by Macy Dong Tuesday, August 9, 2011 7:49 AM
    Monday, August 1, 2011 8:59 PM

All replies

  • try this

     

    SELECT * FROM ReturnConfTbl where Tdate >= #01/08/2011 0:00:00# and Tdate <= #01/08/2011 23:59:59#
    


     

    or

    SELECT * FROM ReturnConfTbl where Tdate BETWEEN #01/08/2011 0:00:00# AND #01/08/2011 23:59:59#


    Monday, August 1, 2011 1:50 PM
  • So, you are looking for records on January 8, 2011.  Access interprets literal dates ## as mm.dd.yyyy format and not dd/mm/yyyy format.  The best way to handle this is to always use yyyy-mm-dd format when you are using literal dates.

    Take a look at this article on International Dates in Access at:
       http://allenbrowne.com/ser-36.html


    John Spencer Access MVP 2002-2005, 2007-2011
    • Marked as answer by Macy Dong Tuesday, August 9, 2011 7:48 AM
    Monday, August 1, 2011 7:58 PM
  • Also note that if there is a time in addition to the date, you should rather use, in order to get anything for the first of August 2011 (note that # assumes that you are using the US format, by default) :

     

    SELECT * FROM ReturnConfTbl where Tdate >= #08/01/2011# and Tdate < #08/02/2011#

    • Marked as answer by Macy Dong Tuesday, August 9, 2011 7:49 AM
    Monday, August 1, 2011 8:59 PM