date as a parameter in a query string to mysql

date as a parameter in a query string to mysql

Postby jeromekj » Wed 17 Nov 2004 15:19

The date format in mysql is yyyy/MM/dd HH:mm:ss
I am using a parameter to select a set of data in a mysql table.
I formatted the parameter as above.
In VB the string passed to the mysqlcommand could be as follow:
MyComm. "SELECT * FROM MyTable WHERE MyDate > " & Chr(39) & strDate & Chr(39)

where strDate could be "2004/11/17 15:00:00". Then I use this mysqlcommand to fill a mysqldatareader.
But doesn't work: no exception occurs but I got no date in the datareader.

What am I supposed to do to retrive all the rows in MyTable where MyDate is greater then "2004/11/17 15:00:00"?
jeromekj
 

Postby cbamford » Wed 17 Nov 2004 15:36

I have a similar problem at the moment. My SELECT command string is:

SELECT DOC_ID, DOC_DST_ID FROM DOCUMENTS WHERE DOC_DATE BETWEEN @StartDate AND @EndDate.

When I run the select command, I get no rows returned but I have data in the database to satisfy the command. How should dates in where clauses be set from programs ? I use the following:

cMySqlDataAdapter.SelectCommand.Parameters[strParameterName].Value = oParameterValue;

Where, strParameterName would be @StartDate and oParameterValue would be the System.DateTime.Value property.

The DOC_DATE column is a DATE column, not a DATETIME column and I'm setting the parameters on the command using System.DateTime variables. I'm coding in C# under VS.NET 2003.

Any suggestions ? Many thanks in advance for any help.

Chris.

PS: Excellent API, nice work :)
cbamford
 
Posts: 7
Joined: Wed 17 Nov 2004 15:30
Location: Sunny Manchester, England

Postby jeromekj » Wed 17 Nov 2004 17:12

I solved the problem by myself.
In VB I used this string:
mycommand.CommandText = "SELECT * FROM MyTable WHERE MyDate > " & Chr(39) & strDate & Chr(39)

where strDate is a string formatted like "yyyy/MM/dd HH:mm:ss"

If works pretty well.
:wink:
jeromekj
 

Postby cbamford » Wed 17 Nov 2004 17:43

I just solved my problem as well. My code was working all along, I was just choosing the wrong date in my UI :oops:
cbamford
 
Posts: 7
Joined: Wed 17 Nov 2004 15:30
Location: Sunny Manchester, England


Return to dotConnect for MySQL