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 4 of 4
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Problems (again!)

    Dates are really doing my head in!!

    I have a problem running this.

    SELECT Count(tblAdvert.ModelID) FROM tblAdvert WHERE tblAdvert.ModelID = 5 AND tblAdvert.Date >= '" & NOW()-14 &"'

    This should count the number of adverts in the last 14 days. I thought it was working fine until we moved onto a new month - February. It won't show any adverts in the last 14 days that are in this month.

    I wrote this little script to test:

    <%response.write NOW()%>
    <%response.write "<BR>"%>
    <%response.write NOW()-14%>
    <%response.write "<BR>"%>
    <% if "01/02/03 09:30:48" <= NOW()-14 then
    response.write "Outside the 14 days"
    else
    response.write "Within the 14 days"
    end if
    %>

    It returns the current date and time and the same 14 days ago. When I tested 01/02/03 09:30:48 - it returned "Outside the 14 days"

    I have tried using the datediff function. This works when i query it in Access but doesn't for some reason when i put it in my asp page.

    e.g.

    SELECT Count(tblAdvert.ModelID) FROM tblAdvert WHERE tblAdvert.ModelID = 5 AND DateDiff("d",tblAdvert.Date,NOW()) <= 14;

    Has anyone got any ideas to count the adverts in the last 14 days? Like i said earlier it will work ok until we hit a new month

    I'm confused!

  • #2
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Try this maybe?:

    SELECT Count(tblAdvert.ModelID) AS myCount FROM tblAdvert WHERE tblAdvert.ModelID = 5 AND DATEDIFF(Day,getdate(),tblAdvert.Date) <= 14

    P.S. I didn't test it, but something like that should work...
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Location
    UK
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts
    whammy,

    cheers for that, it now returns

    "Undefined function 'getdate' in expression."

    I have defined getdate by doing "Dim getdate" but it still gives the error.

    Any ideas?

  • #4
    Senior Coder
    Join Date
    Jun 2002
    Location
    41° 8' 52" N -95° 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Oh.... that's SQL Server, not sure how to do it in Access? Now() maybe?!?
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  

    Posting Permissions

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