Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Aug 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Problem with trying to date ranges using BETWEEN...AND...

    Hi there,

    I'm tearing my hair out over a simple issue, hoping someone can help: I have a JetSQL stmt where the BETWEEN...AND... just won't work.

    I have registration records between 8/08/2005 and 11/08/2005, and I want to be able to do date ranges. BUT when I use "SELECT * FROM registrations WHERE createdate BETWEEN #9/08/2005# AND #11/08/2005#" I get no results.

    BUT when I use "SELECT * FROM registrations WHERE createdate BETWEEN #8/08/2005# AND #10/08/2005#", it spits out all the registrations, even ones with a createdate of 11/08/2005!

    What gives? What am I missing? Any wisdom, HUGELY appreciated!

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    it looks like you are defining dates as dd/mm/yyyy and the database is using mm/dd/yyyy.

  • #3
    Regular Coder
    Join Date
    Aug 2002
    Location
    USA
    Posts
    478
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Hi catchy and welcome to the board!

    I agree with miranda and so perhaps change this:

    "SELECT * FROM registrations WHERE createdate BETWEEN #9/08/2005# AND #11/08/2005#"

    To be more like this instead:

    "SELECT * FROM registrations WHERE createdate BETWEEN #" & DateSerial(2005, 8, 9) & "# AND #" & DateSerial(2005, 8, 11) & "#"

    And the DateSerial function's arguments are year, month, day.

    And FYI here's an example setting a date range based on posted fields:

    strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & Request.Form("MinDate") & "#) AND (TheDateField <= #" & Request.Form("MaxDate") & "#)"

    And here's an example setting a date range to be the current and previous months:

    strSQL = "SELECT * FROM MyTable WHERE (TheDateField >= #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#) AND (TheDateField <= #" & DateSerial(Year(Date()), Month(Date()) - 1, 1) & "#)"
    J. Paul Schmidt
    www.Bullschmidt.com - Freelance Web and Database Developer
    www.Bullschmidt.com/DevTip.asp - Classic ASP Design Tips


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •