| Author |
 Topic Search  Topic Options
|
zaboss 
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
 Post Options
 Thanks(0)
 Quote  Reply
 Topic: Problem with dates
Posted: 09 December 2003 at 1:03am |
I have a problem with dates. The sql query below should select from an mySQL db all the records for which the DataExpirarii (expiration date) value is greater than present day. Instead, it selects all of them. What is wrong?
Set RS1 = MyConn.execute("Select NumeFirma, Pozitia, DataIntroducerii, ID From Oferte where DataExpirarii > "& Date &" Order By DataIntroducerii DESC Limit 10")
|
|
|
|
 |
Paul Lush
Mod Builder Group
Joined: 24 September 2003
Status: Offline
Points: 59
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:05am |
try putting the date in ' s
eg ' " & date & " '
then make sure you have a valid date with seperators, eg "1/1/2003" or "1-1-2003" instead of "1 1 2003"
Without the actual date value (for what we know , date could contain "wibble slobber"), its hard to tell.
|
|
zaboss
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:14am |
|
It doesn't work with '. It triggers the "Exception occur" error. Also, Response.writing both dates shows them correct 12/3/2003 and 12/09/2003. The field in the mySQL db is a Date field.
|
|
|
|
Paul Lush
Mod Builder Group
Joined: 24 September 2003
Status: Offline
Points: 59
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:27am |
the ' was my mistake :(
have you tried " & cdate ( date ) & "
?
Im getting the same thing here in my msSQL server but as all of my work needs date selections, I always use BETWEEN
|
|
Paul Lush
Mod Builder Group
Joined: 24 September 2003
Status: Offline
Points: 59
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:32am |
This works
"Select NumeFirma, Pozitia, DataIntroducerii, ID From Oferte where DataExpirarii > convert(datetime , '" & date & "', 121) Order By DataIntroducerii DESC Limit 10"
there are ' in there as well
|
|
Paul Lush
Mod Builder Group
Joined: 24 September 2003
Status: Offline
Points: 59
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:33am |
Oh, make sure that you us US MM/DD/YYYY formatting. That works in my msSQL, hard to tell in mySQL as I dont have it installed
|
|
zaboss
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:34am |
Nope, it doesn't work. The funny thing is that if I put there "2003-12-09" it works. I tryed to put it in a string with datePart, like in:
data = DatePart("yyyy", Date) &"-"& DatePart("m", Date) &"-"&DatePart("d", Date) and then have the query with
"& data &"
but still does not work.
|
|
|
|
zaboss
Senior Member
Joined: 20 August 2002
Location: Romania
Status: Offline
Points: 454
|
Post Options
Thanks(0)
Quote Reply
Posted: 09 December 2003 at 4:39am |
|
The inner format for dates of MySQL is yyyy-mm-dd.
|
|
|
|
