Selecting Between dates in MySQL

[Afraits]

What does sSQLFilter contain once the variables have been dropped in?

[penfold69]

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 won't 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

[EFileTahi-A]

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]

Is the # required in MYSQL? I know you need it in Access, but forgive my MYSQL ignorance...?

[EFileTahi-A]

Is the # required in MYSQL? I know you need it in Access' date=' but forgive my MYSQL ignorance...?[/quote']

 

Well, I can't 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...

[EFileTahi-A]

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 won't 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

 

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' ???

[penfold69]

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.

[EFileTahi-A]

Ok, tks buddies, it's working...

 

- THREAD RESOLVED -