SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    pull records between two dates

    I'm trying to pull only records from a database that fall between 2 dates. Heres my code:

    If request.form("range") <> "" then
    SQL = "SELECT * FROM tt"
    rs.Open SQL, DC, 1, 3
    bsearchdate = request.form("bmonth") & "/" & request.form("bday") & "/" & request.form("byear")
    esearchdate = request.form("emonth") & "/" & request.form("eday") & "/" & request.form("eyear")
    countem2 = 0
    Do UNTIL rs.EOF
    comparedate = FormatDateTime(rs("datesubmitted"), "2")
    If comparedate > bsearchdate AND comparedate < esearchdate then
    response.write comparedate & "<BR>"
    countem2 = countem2 + 1
    End if
    rs.movenext
    loop
    response.write "<BR><BR><CENTER>Your search for Trouble Tickets between <B>" & bsearchdate & "</B> and <B>" & esearchdate & "</B> returned <B>" & countem2 & "</B> results</CENTER>"
    response.end
    End if

    For some reason it will only pull some dates. I'm using access 2000 and my datesubmitted fields are date/time fields. The bsearchdate is begin search date and the esearchdate is the end search date. Not having any luck with this working. Any suggestions? Thanks

  2. #2
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    After further review of my page it seems to only be compared the month. Because it will work fine until I try october or beyond then it will only show january. I think it reads the 1 from octobers 10 and compares that to januarys 1. Not really sure why its doing this....


    ps. I'm not a sitepoint addict

  3. #3
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    errr I'm NOW a sitepoint addict!


    bad day

  4. #4
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Would it not be easier to get only the relevant dates from the db rather than getting * and then checking each record? This would reduce the script execution time if nothing else.

    So...

    [VBS]
    bsearchdate = request.form("byear") & "/" & request.form("bmonth") & "/" & request.form("bday")
    esearchdate = request.form("eyear") & "/" & request.form("emonth") & "/" & request.form("eday")

    SQL = "SELECT * FROM tt WHERE date_field >='" & bsearchdate & "' AND <= '" & esearchdate & "'"
    [/VBS]

    ...should work ok with a bit of editing. Please note the dates are reversed (y/m/d) as this is the required format for access/sqlServer.


    You can do the count by using rs.movelast and rs.recordcount which will tidy it up a bit.

    Hope this helps.

    Ed.

  5. #5
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,294
    Mentioned
    123 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by mrTed
    Hi,

    Would it not be easier to get only the relevant dates from the db rather than getting * and then checking each record? This would reduce the script execution time if nothing else.

    So...

    [VBS]
    bsearchdate = request.form("byear") & "/" & request.form("bmonth") & "/" & request.form("bday")
    esearchdate = request.form("eyear") & "/" & request.form("emonth") & "/" & request.form("eday")

    SQL = "SELECT * FROM tt WHERE date_field >='" & bsearchdate & "' AND <= '" & esearchdate & "'"
    [/VBS]

    ...should work ok with a bit of editing. Please note the dates are reversed (y/m/d) as this is the required format for access/sqlServer.
    I'd be careful about that date format. My Access and SQL servers do not have the dates in that format.

    williamsba,

    I'd do like mrTed suggested and let the SQL server do the work for you, but if you want to keepg doing it the way you are, convert all the fields to dates to make sure you're comparing like values, ie

    comparedate = cdate(FormatDateTime(rs("datesubmitted"), "2"))
    If comparedate > cdate(bsearchdate) AND comparedate < cdate(esearchdate) then
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2002
    Posts
    42
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Dave,

    Yes, I get what you are saying. If you do a SELECT Date FROM... query, when you display the returned value(s) it usually gives you a fairly rational interpretation as specified in your server regional settings as the data gets interpreted from the stored format into the display format.

    Access and SQL Server will always store the date in its raw format the same for the specified data type on. I have found that if you try to find records using a date which fits your regional or display settings as a parameter, you will often return no records. You usually need to do some manipulation of the information as you are not comparing against a date value as you would see if you were to retrieve and display date information.

    If you do SQLServer queries you can often use the CAST() and CONVERT() functions to get around any peculiarities in the dste parameter as these will try to convert any supplied argument into a valid parameter for the data type.

    I have struggled with using dates as query parameters in the past, especially when I have not visited the topic for any length of time (age & memory don't work well together!) and I have seen enough questions about it on forums to know it is a bit of a nightmare at times.

    Ed.

  7. #7
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    I got it to work so i thought I would post the working code that I used. I did the formula within the SQL statement as follows:

    SQL = "SELECT * FROM table WHERE datesubmitted BETWEEN #"&bsearchdate&"# AND #"&esearchdate&"#"


    datesubmitted - date being checked
    bsearchdate - is the begin date
    esearchdate - is the end date

    so datesubmitted must fall in between bsearchdate and esearchdate.

    Thanks for all the help


Bookmarks

Posting Permissions

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