Reputation: 1942
I'm getting a weird return when executing this query :
SELECT * FROM rrp
WHERE end > "2012-12-31"
nothing is returned, although I have one row on this table which "end" column is greater than "2012-12-31"
:
rrp
id_r | id__b | start | end | quantity
27 29 2012-01-01 2012-05-05 1
31 29 2012-11-01 2013-01-01 1
EDIT : start
and end
are date
fields
EDIT : I used wrong database for my tests => wrong result the issue was coming from Zend_Date when adding a day to a date:
$start = "2012-12-31"; $nStart = new Zend_Date($start, "YYYY-MM-dd"); $end = new Zend_Date($nStart); $end->addDay(1);
When i echoed $end : echo $end->get("YYYY-MM-dd");
it outputs 2013-12-31
Upvotes: 0
Views: 413
Reputation: 146660
If end
is a DATE column, it should work as expected:
SELECT
STR_TO_DATE('2013-01-01', '%Y-%m-%d') < "2012-12-31",
STR_TO_DATE('2012-05-05', '%Y-%m-%d') < "2012-12-31"
... returns 0, 1
in my box.
The only possible flaw I can think of is that your system's default date format is not %Y-%m-%d
:
SELECT @@DATE_FORMAT
In that case, you need to specify a format every time:
SELECT *
FROM rrp
WHERE end > STR_TO_DATE('2012-12-31', '%Y-%m-%d')
Upvotes: 2
Reputation: 681
Most likely an issue with how the dates are formatted
This should help
http://dev.mysql.com/doc/refman/5.0/en/using-date.html
Upvotes: 2