ASP MYSQL date in query

Discussion in 'ASP General' started by M. Savas Zorlu, Feb 11, 2009.

  1. Hi,

    I am changinf my databse from ACCESS to MysQl.

    There is one query that I couldnt manage to convet to Mysql syntax. Can
    anyone help me please?

    It is:

    strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "
     
    M. Savas Zorlu, Feb 11, 2009
    #1
    1. Advertising

  2. M. wrote on Wed, 11 Feb 2009 17:39:50 +0200:

    > Hi,


    > I am changinf my databse from ACCESS to MysQl.


    > There is one query that I couldnt manage to convet to Mysql syntax. Can
    > anyone help me please?


    > It is:


    > strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "



    The DATEDIFF function in MySQL only returns the number of days and cannot be
    adjusted. What I think will work (not tested) is

    strsql = strsql & " AND DATE_ADD(NOW(), INTERVAL 1 HOUR) > A_DATE_END "

    which adds 1 hour to NOW() (the current time, although you could use
    CURRENT_TIMESTAMP instead) and then sees if this is greater than the value
    of A_DATE_END.

    Given that you're constructing SQL "on the fly" I would also urge you to
    read up on SQL Injection and ensure that all your SQL building code is
    resilient to malicious requests.

    --
    Dan
     
    Daniel Crichton, Feb 11, 2009
    #2
    1. Advertising

  3. Many thanks Dan,

    it did not give the correct result as you sent me but when I changed it
    to following it worked:

    strsql = strsql & " AND DATE_ADD(A_DATE_END, INTERVAL 1 HOUR) > NOW() "

    Also thanks for the advice on sql injection.

    Regards,

    Savas


    Daniel Crichton yazmış:
    > M. wrote on Wed, 11 Feb 2009 17:39:50 +0200:
    >
    >> Hi,

    >
    >> I am changinf my databse from ACCESS to MysQl.

    >
    >> There is one query that I couldnt manage to convet to Mysql syntax. Can
    >> anyone help me please?

    >
    >> It is:

    >
    >> strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "

    >
    >
    > The DATEDIFF function in MySQL only returns the number of days and cannot be
    > adjusted. What I think will work (not tested) is
    >
    > strsql = strsql & " AND DATE_ADD(NOW(), INTERVAL 1 HOUR) > A_DATE_END "
    >
    > which adds 1 hour to NOW() (the current time, although you could use
    > CURRENT_TIMESTAMP instead) and then sees if this is greater than the value
    > of A_DATE_END.
    >
    > Given that you're constructing SQL "on the fly" I would also urge you to
    > read up on SQL Injection and ensure that all your SQL building code is
    > resilient to malicious requests.
    >
     
    M. Savas Zorlu, Feb 12, 2009
    #3
  4. Odd, your original query was looking for the number of hours between Now()
    and A_DATE_END to be greater than 0, and so A_DATE_END would be after Now().
    Take the following examples:

    Now() = 12 Feb 2009 14:00
    A_DATE_END = 12 Feb 2009 13:45

    In your original query, DateDiff("h",Now(),A_DATE_END) would return -1 and
    the statement would be False.

    In your replacement, the statement would equate to True because you are
    adding 1 hour to A_DATE_END and that will be after the value of Now(). The
    replacement I suggested would be False too, and so match your original.

    Now, take the example:

    Now() = 12 Feb 2009 14:00
    A_DATE_END = 12 Feb 2009 14:45

    In the original version this would be False still, because DateDiff will
    return 0. In my version it's False too, and again in yours it's True.

    I think you need to check your results, as I can't see how your change could
    be giving you the same results as you had in your original Access query.

    Dan

    M. wrote on Thu, 12 Feb 2009 07:32:01 +0200:

    > Many thanks Dan,


    > it did not give the correct result as you sent me but when I changed it
    > to following it worked:


    > strsql = strsql & " AND DATE_ADD(A_DATE_END, INTERVAL 1 HOUR) > NOW() "


    > Also thanks for the advice on sql injection.


    > Regards,


    > Savas



    > Daniel Crichton yazmis:
    >> M. wrote on Wed, 11 Feb 2009 17:39:50 +0200:


    >>> Hi,


    >>> I am changinf my databse from ACCESS to MysQl.


    >>> There is one query that I couldnt manage to convet to Mysql syntax.
    >>> Can anyone help me please?


    >>> It is:


    >>> strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "



    >> The DATEDIFF function in MySQL only returns the number of days and
    >> cannot be adjusted. What I think will work (not tested) is


    >> strsql = strsql & " AND DATE_ADD(NOW(), INTERVAL 1 HOUR) > A_DATE_END
    >> "


    >> which adds 1 hour to NOW() (the current time, although you could use
    >> CURRENT_TIMESTAMP instead) and then sees if this is greater than the
    >> value of A_DATE_END.


    >> Given that you're constructing SQL "on the fly" I would also urge you
    >> to read up on SQL Injection and ensure that all your SQL building
    >> code is resilient to malicious requests.
     
    Daniel Crichton, Feb 12, 2009
    #4
  5. Indeed it is odd, but so far it has been giving the correct result in my
    access version. Or I think it has been. not sure now. I am puzzled

    Basicly this query checks if the logged user has published and add, has
    not paid for it.

    Perhaps if I give yout the full query it would make more sense

    strSql = "SELECT COUNT(AD_ID) AS unpaid "
    strSql = strSql & " FROM tblADS"
    strSql = strSql & " WHERE A_PAID = 0 "
    strSql = strSql & " AND A_USER = " & getmembernumber(activeuser) & ""
    strSql = strSql & " AND A_STATUS > 0 "
    strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "

    so probably this query would still give a warning to user even still the
    end date is expired. which it shouldn't.

    am I right?

    The new query is:
    strSql = "SELECT COUNT(AD_ID) AS unpaid "
    strSql = strSql & " FROM tblADS"
    strSql = strSql & " WHERE A_PAID = 0 "
    strSql = strSql & " AND A_USER = " & getmembernumber(strdbntusername) & ""
    strSql = strSql & " AND A_STATUS > 0 "
    strsql = strsql & " AND DATE_ADD(A_DATE_END, INTERVAL 1 HOUR) > NOW() "


    So it should stop bugging the user for unpaid ads if the A_DATE_END is
    expired.

    Is this correct?

    Daniel Crichton yazmış:
    > Odd, your original query was looking for the number of hours between Now()
    > and A_DATE_END to be greater than 0, and so A_DATE_END would be after Now().
    > Take the following examples:
    >
    > Now() = 12 Feb 2009 14:00
    > A_DATE_END = 12 Feb 2009 13:45
    >
    > In your original query, DateDiff("h",Now(),A_DATE_END) would return -1 and
    > the statement would be False.
    >
    > In your replacement, the statement would equate to True because you are
    > adding 1 hour to A_DATE_END and that will be after the value of Now(). The
    > replacement I suggested would be False too, and so match your original.
    >
    > Now, take the example:
    >
    > Now() = 12 Feb 2009 14:00
    > A_DATE_END = 12 Feb 2009 14:45
    >
    > In the original version this would be False still, because DateDiff will
    > return 0. In my version it's False too, and again in yours it's True.
    >
    > I think you need to check your results, as I can't see how your change could
    > be giving you the same results as you had in your original Access query.
    >
    > Dan
    >
    > M. wrote on Thu, 12 Feb 2009 07:32:01 +0200:
    >
    >> Many thanks Dan,

    >
    >> it did not give the correct result as you sent me but when I changed it
    >> to following it worked:

    >
    >> strsql = strsql & " AND DATE_ADD(A_DATE_END, INTERVAL 1 HOUR) > NOW() "

    >
    >> Also thanks for the advice on sql injection.

    >
    >> Regards,

    >
    >> Savas

    >
    >
    >> Daniel Crichton yazmis:
    > >> M. wrote on Wed, 11 Feb 2009 17:39:50 +0200:

    >
    > >>> Hi,

    >
    > >>> I am changinf my databse from ACCESS to MysQl.

    >
    > >>> There is one query that I couldnt manage to convet to Mysql syntax.
    > >>> Can anyone help me please?

    >
    > >>> It is:

    >
    > >>> strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "

    >
    >
    > >> The DATEDIFF function in MySQL only returns the number of days and
    > >> cannot be adjusted. What I think will work (not tested) is

    >
    > >> strsql = strsql & " AND DATE_ADD(NOW(), INTERVAL 1 HOUR) > A_DATE_END
    > >> "

    >
    > >> which adds 1 hour to NOW() (the current time, although you could use
    > >> CURRENT_TIMESTAMP instead) and then sees if this is greater than the
    > >> value of A_DATE_END.

    >
    > >> Given that you're constructing SQL "on the fly" I would also urge you
    > >> to read up on SQL Injection and ensure that all your SQL building
    > >> code is resilient to malicious requests.

    >
    >
     
    M. Savas Zorlu, Feb 12, 2009
    #5
  6. Ah, I see now. Because DateDiff returns a zero even if A_DATE_END is 59 mins
    and 59 secs in the future, your replacement is correct. I was looking it
    back to front, and assumed that DateDiff returns a 1 as soon a A_DATE_END is
    greater than Now().

    Dan

    M. wrote on Thu, 12 Feb 2009 13:43:25 +0200:

    > Indeed it is odd, but so far it has been giving the correct result in
    > my access version. Or I think it has been. not sure now. I am puzzled


    > Basicly this query checks if the logged user has published and add, has
    > not paid for it.


    > Perhaps if I give yout the full query it would make more sense


    > strSql = "SELECT COUNT(AD_ID) AS unpaid "
    > strSql = strSql & " FROM tblADS"
    > strSql = strSql & " WHERE A_PAID = 0 " strSql = strSql & " AND A_USER = "
    > & getmembernumber(activeuser) & ""
    > strSql = strSql & " AND A_STATUS > 0 "
    > strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "


    > so probably this query would still give a warning to user even still
    > the end date is expired. which it shouldn't.


    > am I right?


    > The new query is:
    > strSql = "SELECT COUNT(AD_ID) AS unpaid "
    > strSql = strSql & " FROM tblADS"
    > strSql = strSql & " WHERE A_PAID = 0 " strSql = strSql & " AND A_USER = "
    > & getmembernumber(strdbntusername) &
    > ""
    > strSql = strSql & " AND A_STATUS > 0 "
    > strsql = strsql & " AND DATE_ADD(A_DATE_END, INTERVAL 1 HOUR) > NOW() "



    > So it should stop bugging the user for unpaid ads if the A_DATE_END is
    > expired.


    > Is this correct?


    > Daniel Crichton yazmis:
    >> Odd, your original query was looking for the number of hours between
    >> Now()
    >> and A_DATE_END to be greater than 0, and so A_DATE_END would be after
    >> Now().
    >> Take the following examples:


    >> Now() = 12 Feb 2009 14:00
    >> A_DATE_END = 12 Feb 2009 13:45


    >> In your original query, DateDiff("h",Now(),A_DATE_END) would return
    >> -1 and the statement would be False.


    >> In your replacement, the statement would equate to True because you
    >> are adding 1 hour to A_DATE_END and that will be after the value of
    >> Now(). The replacement I suggested would be False too, and so match
    >> your original.


    >> Now, take the example:


    >> Now() = 12 Feb 2009 14:00
    >> A_DATE_END = 12 Feb 2009 14:45


    >> In the original version this would be False still, because DateDiff
    >> will return 0. In my version it's False too, and again in yours it's
    >> True.


    >> I think you need to check your results, as I can't see how your
    >> change could be giving you the same results as you had in your
    >> original Access query.


    >> Dan


    >> M. wrote on Thu, 12 Feb 2009 07:32:01 +0200:


    >>> Many thanks Dan,


    >>> it did not give the correct result as you sent me but when I changed
    >>> it to following it worked:


    >>> strsql = strsql & " AND DATE_ADD(A_DATE_END, INTERVAL 1 HOUR) >
    >>> NOW() "


    >>> Also thanks for the advice on sql injection.


    >>> Regards,


    >>> Savas



    >>> Daniel Crichton yazmis:
    >>>> M. wrote on Wed, 11 Feb 2009 17:39:50 +0200:


    >>>>> Hi,


    >>>>> I am changinf my databse from ACCESS to MysQl.


    >>>>> There is one query that I couldnt manage to convet to Mysql
    >>>>> syntax.
    >>>>> Can anyone help me please?


    >>>>> It is:


    >>>>> strsql = strsql & " AND DateDiff('h', now() , A_DATE_END) > 0 "



    >>>> The DATEDIFF function in MySQL only returns the number of days and
    >>>> cannot be adjusted. What I think will work (not tested) is


    >>>> strsql = strsql & " AND DATE_ADD(NOW(), INTERVAL 1 HOUR) >
    >>>> A_DATE_END "


    >>>> which adds 1 hour to NOW() (the current time, although you could
    >>>> use
    >>>> CURRENT_TIMESTAMP instead) and then sees if this is greater than
    >>>> the value of A_DATE_END.


    >>>> Given that you're constructing SQL "on the fly" I would also urge
    >>>> you to read up on SQL Injection and ensure that all your SQL
    >>>> building code is resilient to malicious requests.
     
    Daniel Crichton, Feb 12, 2009
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Matt
    Replies:
    1
    Views:
    608
    Matthew Speed
    Nov 8, 2003
  2. Peter Grison

    Date, date date date....

    Peter Grison, May 28, 2004, in forum: Java
    Replies:
    10
    Views:
    3,278
    Michael Borgwardt
    May 30, 2004
  3. Matt
    Replies:
    2
    Views:
    519
    Pete Becker
    Nov 8, 2003
  4. Matt
    Replies:
    3
    Views:
    724
    Richard Heathfield
    Nov 8, 2003
  5. Love4llamas
    Replies:
    0
    Views:
    807
    Love4llamas
    Oct 13, 2011
Loading...

Share This Page