CODEWITHSUNDEEP
mysqlvb.net
MrNeedsaHand
MrNeedsaHand

Reputation: 45

Statement Passed to Mysql Database not filling DataTable

 dbQuery = "SELECT * FROM table WHERE date BETWEEN " & startDate & " AND " & endDate & ""

When i execute this statement in NaviCat it does not return any values either. Tho the values do exist on the 19th and 22nd of June in 2015. am i sending the query wrong???

Upon debugging the query is sent as follows.

select * from table where date between 6/1/2015 and 6/31/2015

Upvotes: 0

Views: 103

Answers (3)

spencer7593
spencer7593

Reputation: 108370

To get the comparisons to operate on "date" values, the way you expect, you need to convert the strings into DATE values.

The problem in your query is that the the comparison is being done in a numeric context, rather than on DATE values. Your query is roughly equivalent to

SELECT * 
  FROM table
 WHERE date BETWEEN 0.0029776674937965260545905707 AND 0.000096053790122469

That is, the literals in the "between" comparison are evaluating to numeric values (e.g. six divided by thirty-one divided by two thousand fifteen). To illustrate this, consider the output from this:

 SELECT '6/12/2015'
      , 6/1/2015
      , 6/31/2015
      , '6/12/2015' BETWEEN 6/1/2015 AND 6/31/2015 
      , '6/12/2015' BETWEEN 5.9 AND 6.2

If you wrap the literals in quotes (as suggested in another answer) will also not give expected behavior.

SELECT * 
  FROM table
 WHERE date BETWEEN '6/1/2015' AND '6/31/2015'

The problem with numeric evaluation and comparison is fixed, but now the comparison will be done in string context. This will return some date values in the month of June, but it will also exclude some values that we would (reasonably) expect to be returned, such as '6/4/2015'. As a demonstration, consider:

 SELECT '6/4/2015' BETWEEN '6/1/2015' AND '6/31/2015'

MySQL has builtin datatypes designed for handling "datetime" type values... DATE, DATETIME and TIMESTAMP. These datatypes make working with "date" and "datetime" values easy, and make comparisons simple.

Use DATE datatype and STR_TO_DATE function

To get the comparisons done as "date" values, convert the column value and the literals into DATE datatype.

The MySQL STR_TO_DATE function will convert a string into a DATE, based on the specified format.

To get "date" comparisons to work "correctly" on string values, you could do something like this:

     SELECT t.* 
       FROM table t 
      WHERE STR_TO_DATE( t.date     ,'%m/%d/%Y')
    BETWEEN STR_TO_DATE( '6/1/2015' ,'%m/%d/%Y')
        AND STR_TO_DATE( '6/3/2015' ,'%m/%d/%Y')

Note the single quotes around the literals, those are needed for those literals to be seen as strings, and not numeric expressions. The real trick is the MySQL STR_TO_DATE function.

Note that MySQL cannot make effective use of an index to satisfy the predicate, the STR_TO_DATE function has to be evaluated for every row in the table.

If this column was defined as DATE datatype, then we could do the comparison on the bare column, and MySQL can make use of a range scan operation on a suitable index.

Upvotes: 1

Douglas Barbin
Douglas Barbin

Reputation: 3615

Since your column is varchar, you will need to cast it to the correct type if you want comparisons to work correctly.

dbQuery = "SELECT * FROM table WHERE date BETWEEN CAST('" & startDate & "' as DATE) AND CAST('" & endDate & "' as DATE)"

Alternately, since you are using MySQL, you could use the STR_TO_DATE function like so:

dbQuery = "SELECT * FROM table WHERE date BETWEEN STR_TO_DATE('" & startDate & "', '%m/%d/%Y') AND STR_TO_DATE('" & endDate & "', '%m/%d/%Y')"

The main difference between those two is that trying to CAST an invalid string into a date will result in an error, while doing the same thing but using STR_TO_DATE will substitute NULL for the invalid string, which will result in your query returning no rows.

As others have mentioned, you're not doing yourself any favors by storing dates as strings. In most languages, it is trivially easy to convert a date to a string (i.e., for display purposes), but the inverse is not always true. Since you are storing dates as strings, the best you will be able to do is to cast and hope that all of your strings are valid dates.

The SQL standard includes data types for dates, and for good reason. You would be well-advised to use them.

Upvotes: 3

HashSu
HashSu

Reputation: 1517

As said by Steve the field type is bad for a date. Anyway Since date type is varchar you might try this:

select * from table where date >= '6/1/2015' and date <= '6/31/2015';

Upvotes: -1

Related Questions