SQL Question

Discussion in 'OT Technology' started by sablehonda, Feb 13, 2004.

  1. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    I have an Access database which has a table with timestamps (Date/Time access data type). The format for these timestamps is
    mm/dd/yyyy hh:mm:ss

    I have a VB application with a calendar that will allow the user to select a date and return all rows from the database with the selected date. The format for the selected date is
    mm/dd/yyyy

    What SQL code will get these rows from the database? I've tried =, LIKE, and BETWEEN statements between the two entities with no luck.
     
  2. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Assuming we are using date type variable (and if not you pretty much have to):

    between [dateprovided] and dateadd("s",-1,dateserial(year([dateprovided]),month([dateprovided]),day([dateprovided])+1))
     
  3. sablehonda

    sablehonda New Member

    Joined:
    Sep 21, 2002
    Messages:
    8,514
    Likes Received:
    0
    Location:
    Chicago
    Thanks 5Gen; that worked well.

    Date/time processing is definetely not a strong point of mine :)
     
  4. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Not sure what would have been faster but you could also try formatting the field and stripping the time off and then just comparing it with the [dateprovided]

    ie:

    format([datestampfieldindatabase],"short date")
     

Share This Page