ASP MYSQL date in query

M

M. Savas Zorlu

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 "
 
D

Daniel Crichton

M. wrote on Wed, 11 Feb 2009 17:39:50 +0200:
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?
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

M. Savas Zorlu

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ış:
 
D

Daniel Crichton

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:
 
M

M. Savas Zorlu

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ış:
 
D

Daniel Crichton

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.
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,480
Members
44,900
Latest member
Nell636132

Latest Threads

Top