CODEWITHSUNDEEP
mysqldatecomparison
Jerec TheSith
Jerec TheSith

Reputation: 1942

Compare dates from different years in mySQL

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 : startand endare 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

Answers (2)

Álvaro González
Álvaro González

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

Brad Fox
Brad Fox

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

Related Questions