Selecting Between dates in MySQL

Use.... Parameters.....

Or.. Read the MySql documentation.

DATE / DATETIME fields in mysql are stored in the format: YYYY-MM-DD HH:MM:SS.xx as a *STRING*

Therefore, the ToString() function of cbx_date1.Value (which is being implicitly returned) will return something to the ilk of: MM/DD/YYYY HH:MM:SS.xx

As you see, if you look closely.. those wont EVER compare correctly.. so..


1. Use parameters
2. Write a function to convert a .NET Date/Time to a MySql Compatible Date/Time

p.s. - the actual answer to the delimiting question - MySQL uses s to delimit a date, not ##s
 
I have searched to forum for answers but all solutions found fail to work...

sSQLFilter = "WHERE datdoc >= #" + this.cbx_date1.Value + "# AND datdoc <= #" + this.cbx_date1.Value + "#";

sSQLFilter = "WHERE datdoc BETWEEN #" + this.cbx_date1.Value + "# AND #" + this.cbx_date1.Value + "#"

It always throws me an Syntax error...

Please aid...
Thank you...
 
samsmithnz said:
Is the # required in MYSQL? I know you need it in Access, but forgive my MYSQL ignorance...?
Click to expand...

Well, I cant really tell, I also tryed it without the "#", it does not retun me any sintax error all right, but also, it does not return me nothing, when it should...
 
penfold69 said:
Use.... Parameters.....

Or.. Read the MySql documentation.

DATE / DATETIME fields in mysql are stored in the format: YYYY-MM-DD HH:MM:SS.xx as a *STRING*

Therefore, the ToString() function of cbx_date1.Value (which is being implicitly returned) will return something to the ilk of: MM/DD/YYYY HH:MM:SS.xx

As you see, if you look closely.. those wont EVER compare correctly.. so..


1. Use parameters
2. Write a function to convert a .NET Date/Time to a MySql Compatible Date/Time

p.s. - the actual answer to the delimiting question - MySQL uses s to delimit a date, not ##s
Click to expand...

Ok, I just need to know the right syntax now... is this ok?:
SELECT * FROM _tablename_ WHERE _columnName_ BETWEEN yyyy-mm-dd hh:mm:ss AND yyyy-mm-dd hh:mm:ss ???
 
From the MySQL Docs online at: http://dev.mysql.com/doc/mysql/en/comparison-operators.html

expr BETWEEN min AND max

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described at the beginning of this section, but applied to all the three arguments. Note: Before MySQL 4.0.5, arguments were converted to the type of expr instead.

mysql> SELECT 1 BETWEEN 2 AND 3;
-> 0
mysql> SELECT b BETWEEN a AND c;
-> 1
mysql> SELECT 2 BETWEEN 2 AND 3;
-> 1
mysql> SELECT 2 BETWEEN 2 AND x-3;
-> 0

So, I would assume that your syntax is correct.

B.
 
Back
Top