SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Mar 2005
    Posts
    14
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    asp and mysql - checking within start and end dates

    Hi

    i am converting an asp ecommerce site to use mySQL instead of access

    i am mostly doing ok but have run into this problem with the SQL used to check a voucher code is valid - ie to check it is within date

    here is the original asp / access code:


    <&#37;
    Dim VouchersRS__param1
    VouchersRS__param1 = "0"
    If (Request("Voucher") <> "") Then
    VouchersRS__param1 = Request("Voucher")
    End If
    %>
    <%
    Dim VouchersRS
    Dim VouchersRS_numRows

    Set VouchersRS = Server.CreateObject("ADODB.Recordset")
    VouchersRS.ActiveConnection = MM_Cart_STRING

    VouchersRS.Source = "SELECT * FROM emporium.vouchers WHERE (((DateDiff('d',[StartDate],date()))>=0) AND ((DateDiff('d',[EndDate],date()))<=0)) and VoucherCode='" + Replace(VouchersRS__param1, "'", "''") + "'"

    VouchersRS.CursorType = 0
    VouchersRS.CursorLocation = 2
    VouchersRS.LockType = 1
    VouchersRS.Open()

    VouchersRS_numRows = 0
    %>


    here is the error:

    You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE (((DateDiff('d',[StartDate],GetDateTime))>=0) AND ((DateDiff('d',[EndDate]' at line 1



    can anyone help?

    thanks
    ian

  2. #2
    SitePoint Guru
    Join Date
    Jun 2007
    Posts
    691
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is better question for mySQL group

    "You have an error in your SQL syntax; " Hence not an ASP error.

    Check with MySQL manual or newsgroup to see if the SQL syntax is allowed

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the problem, of course, is that mysql date functions and access date functions are wildly different

    you have this --
    Code:
    WHERE (((DateDiff('d',[StartDate],date()))>=0) 
      AND ((DateDiff('d',[EndDate],date()))<=0))
    i ~think~ what this is trying to do is find rows where the current date is between the start date and end date

    try this --
    Code:
    WHERE CURRENT_DATE BETWEEN StartDate AND EndDate
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •