Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > ASP MYSQL date in query

Reply
Thread Tools

ASP MYSQL date in query

 
 
M. Savas Zorlu
Guest
Posts: n/a
 
      02-11-2009
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 "
 
Reply With Quote
 
 
 
 
Daniel Crichton
Guest
Posts: n/a
 
      02-11-2009
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


 
Reply With Quote
 
 
 
 
M. Savas Zorlu
Guest
Posts: n/a
 
      02-12-2009
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.
>

 
Reply With Quote
 
Daniel Crichton
Guest
Posts: n/a
 
      02-12-2009
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.



 
Reply With Quote
 
M. Savas Zorlu
Guest
Posts: n/a
 
      02-12-2009
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.

>
>

 
Reply With Quote
 
Daniel Crichton
Guest
Posts: n/a
 
      02-12-2009
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.



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql with 1.9.2 query: wrong argument tile Mysql (expected Struct) TypeError Love4llamas Ruby 0 10-13-2011 03:22 AM
Date, date date date.... Peter Grison Java 10 05-30-2004 01:20 PM
Given a date, how to find the beginning date and ending date of that week Matt ASP .Net 1 11-08-2003 09:14 PM
Given a date, how to find the beginning date and ending date of that week Matt C Programming 3 11-08-2003 09:07 PM
Given a date, how to find the beginning date and ending date of that week Matt C++ 2 11-08-2003 08:30 PM



Advertisments