MySql queries with BETWEEN

[nst]

Continuing my stock programm I'm trying to build, I encountered the following problem. To put it simply

this returns as a result a number of rows for the corresponding 'stock_symbol' over the given 'ref_calendar.date'

SELECT
share_prices.stock_symbol,
share_prices.price_date,
ref_calendar.date
FROM
ref_calendar
INNER JOIN share_prices ON (ref_calendar.date_id=share_prices.date_id)
WHERE
(share_prices.stock_symbol = 'BLA') AND
(ref_calendar.date > 2005-01-05)

while this
SELECT
share_prices.stock_symbol,
share_prices.price_date,
ref_calendar.`date`
FROM
ref_calendar
INNER JOIN share_prices ON (ref_calendar.date_id=share_prices.date_id)
WHERE
(share_prices.stock_symbol = 'BLA') AND
(ref_calendar.`date` BETWEEN 2005-01-05 AND 2005-02-04)

and this
SELECT
share_prices.stock_symbol,
share_prices.price_date,
ref_calendar.date
FROM
ref_calendar
INNER JOIN share_prices ON (ref_calendar.date_id=share_prices.date_id)
WHERE
(share_prices.stock_symbol = 'BLA') AND
(ref_calendar.date > 2005-01-05) AND
(ref_calendar.date < 2005-05-01)

return NULL

Why?

[ronaldb66]

Are you sure the date format is correct, and the first query didn't just return all rows? If I recall correctly, literal dates should be specified as strings.
Also, the order of EEYY, MM and DD and the separators are locale dependant, but a simple query on the date column should tell you how dates are represented.

[nst]

You're right. It ignores the filter.

OK. Here is the result I get from a query with no date restrictions.

BLA 0.17 2005-01-03
BLA 0.16 2005-01-04
BLA 0.15 2005-01-05
BLA 0.14 2005-01-07

Apparently date format is correct. As MySql reveals:
type is DATE
format is 0000-00-00


Where the &^*$# is the error? :confused:

[nst]

If I recall correctly, literal dates should be specified as strings.

Do you mean in php? I haven't done it yet, I am testing it first in MySql directly.

[nst]

Problem solved by using ''.

(ref_calendar.`date` BETWEEN '2005-09-05' AND '2005-10-04')

It's always a quotation problem...