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 7 of 7
  1. #1
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date query problems

    Im need to extract all records in a certain month but I keep getting funny outputs if someone could shed some light here it would be aprreciated

    THis SQL produces the line below this
    SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= "&viewdate1&" AND contactDate <="&viewdate2

    SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= #1/8/2003# AND contactDate <= #31/8/2003#

    this output works for august ok displaying the records in that month, however if I select november it displays all records
    if i select before this month it shows no records, why is it displaying all records when I select months after this month??
    I have the same results when i use the 'Between' tag
    Last edited by dawilis; 11-16-2003 at 12:58 PM.

  • #2
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date query problems

    Originally posted by dawilis
    Im need to extract all records in a certain month but I keep getting funny outputs if someone could shed some light here it would be aprreciated

    THis SQL produces the line below this
    SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= "&viewdate1&" AND contactDate <="&viewdate2

    SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate >= #1/8/2003# AND contactDate <= #31/8/2003#

    this output works for august ok displaying the records in that month, however if I select november it displays all records
    if i select before this month it shows no records, why is it displaying all records when I select months after this month??
    I have the same results when i use the 'Between' tag

  • #3
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You should use the like-operator and then check on the month. Like this
    Code:
    wantedmonth='08'     'this value can come from a dropdown or if you need the current month, use month(Now)
    SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate LIKE '%/" & wantedmonth & "/%'"

  • #4
    Senior Coder A1ien51's Avatar
    Join Date
    Jun 2002
    Location
    Between DC and Baltimore In a Cave
    Posts
    2,717
    Thanks
    1
    Thanked 94 Times in 88 Posts
    Is the date being stored in the database in that format?

    That could be the problem.
    Tech Author [Ajax In Action, JavaScript: Visual Blueprint]

  • #5
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    date format

    the date in the d/b is dd/mm/yy
    as in the sql querystring, it will work ok however if i change the format to mm/dd/yy, but still with the problems mentioned above

  • #6
    Regular Coder
    Join Date
    Jul 2002
    Location
    Bunbury W.A
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts

    that worked

    thanks A1ien51 the like worked a treat

  • #7
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ahum, ahum ...

    Anyway, i spotted a possible problem --> the year probably shoulnt be a wildcard.
    so your code would then be
    Code:
    wantedmonth='08'     'this value can come from a dropdown or if you need the current month, use      month(Now)
    wantedyear='03'    'idem but       right(year(Now),2)
    SQL2= "SELECT COUNT(*) AS Contacts FROM Contacts WHERE contactDate LIKE '%/" & wantedmonth & "/" & wantedyear & "'"


  •  

    Posting Permissions

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