mysql date comparison with date_format

Question

I googled and tried several ways to compare date but unfortunately didn't get the result as expected. I have current state of records like following:

        mysql> select date_format(date(starttime),'%d-%m-%Y') from data;

              +-----------------------------------------+
              | date_format(date(starttime),'%d-%m-%Y') |
              +-----------------------------------------+
              | 28-10-2012                              |
              | 02-11-2012                              |
              | 02-11-2012                              |
              | 02-11-2012                              |
              | 03-11-2012                              |
              | 03-11-2012                              |
              | 07-11-2012                              |
              | 07-11-2012                              |

I would like to compare date and therefore do like this:

        mysql> select date_format(date(starttime),'%d-%m-%Y') from data where date_format(date(starttime),'%d-%m-%y') >= '02-11-2012';
               +-----------------------------------------+
               | date_format(date(starttime),'%d-%m-%Y') |
               +-----------------------------------------+
               | 28-10-2012                              |
               | 02-11-2012                              |
               | 02-11-2012                              |
               | 02-11-2012                              |
               | 03-11-2012                              |
               | 03-11-2012                              |
               | 07-11-2012                              |
               | 07-11-2012                              |

I believe that the result should not include '28-10-2012'. Any suggestion? Thanks in advance.

Dressingdown

Answer 1

Your format is fundamentally not a sortable one to start with - you're comparing strings, and the string "28-10-2012" is greater than "02-11-2012".

Instead, you should be comparing dates as dates, and then only converting them into your target format for output.

Try this:

select date_format(date(starttime),'%d-%m-%Y') from data
where date(starttime) >= date '2012-11-02';

(The input must always be in year-month-value form, as per the documentation.)

Note that if starttime is a DATETIME field, you might want to consider changing the query to avoid repeated conversion. (The optimizer may well be smart enough to avoid it, but it's worth checking.)

select date_format(date(starttime),'%d-%m-%Y') from data
where starttime >= '2012-11-02 00:00:00';

(Note that it's unusual to format a date as d-m-Y to start with - it would be better to use y-M-d in general, being the ISO-8601 standard etc. However, the above code does what you asked for in the question.)

Dichlamydeous

@MahmoudGamal: your edit doesn't make sense. Specifying a date literal using the Syntax date '2012-11-02' is valid (in fact it's the SQL standard for specifying date literals). See here: sqlfiddle.com/#!2/d41d8/4020 – Honora

@JonSkeet: You can specify a datetime literal using the SQL standard literal as well: timestamp '2012-11-02 00:00:00' (instead of using a "plain" string). – Honora

@a_horse_with_no_name - Sorry, didn't know that. I removed my edit. – Wheaton

@a_horse_with_no_name: Yes, I considered that - but as that would then be a timestamp rather than a datetime, I wasn't entirely sure it would be appropriate. – Dichlamydeous

@JonSkeet - Sorry for my edit. There was a voice telling my that it will be a bad idea to edit a code written be Jon Skeet. But I didn't listen to it. – Wheaton

@MahmoudGamal: No problem - this is SQL we're talking about, after all... it's a foreign language to me :) – Dichlamydeous

@Rahat: Your edit misses the point that the seleted date format isn't used for comparison at all. Admittedly it's odd to select the date in d-M-Y format anyway, but it appears to be what the OP wants. – Dichlamydeous

It seems we get wrong date if we write the query as: select date_format(date(starttime),'%d-%m-%Y') from data where date(starttime) >= date '2012-11-02'; It works fine if we write: select date_format(date(starttime),'%Y-%m-%d') from data where date(starttime) >= date '2012-11-02'; – Subfloor

@Rahat: What do you mean by "wrong date"? I would expect you to get the same rows, but in a different format. – Dichlamydeous

@JonSkeet :If we filter using: date(starttime),'%d-%m-%Y') >= date '2013-12-1' then the query includes dates that are of 30-11-2013 and others. And if we write date(starttime),'%Y-%m-%d') >= date '2013-12-1' Then it excludes the record prior to '2013-12-1' which is the desired output. – Subfloor

@Rahat: But the filtering and what you select are independent. Look at my code again - it doesn't use date_format in the where clause at all. I'm assuming that either starttime is a string column using a format such that date(starttime) is correct, or it's a datetime column already (the second snippet). Note that the OP accepted this answer as working - do you actually have an example running where it doesn't, or are you just theorizing that it doesn't work? – Dichlamydeous

@JonSkeet: Sorry I missed your first comment. Yes you are right that it has no effect on how you format the date in the select query. However, on a datetime filed I had to filter using the format you mention in the select example. i.e. starttime >= '2012-11-02 00:00:00' did not work for me but date_format(date(starttime),'%Y-%m-%d') >= date '2013-12-1' did. – Subfloor

Answer 2

Use 2012-11-02 instead of 02-11-2012 and you will not need date_format() anymore

Optative

Answer 3

Use the following method :

public function dateDiff ($date1, $date2) {
/* Return the number of days between the two dates: */
  return round(abs(strtotime($date1)-strtotime($date2))/86400);
}  
/* end function dateDiff */

It will help!

Billingsley