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.

Jon Skeet · Accepted Answer · 2013-12-30T08:46:39.280

44

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.)

edited Dec 30 '13 at 08:46

answered Nov 22 '12 at 07:25

Jon Skeet

1,421,763
867
9,128
9,194

1

@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: http://sqlfiddle.com/#!2/d41d8/4020 – Nov 22 '12 at 07:47
@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). – Nov 22 '12 at 07:49
@a_horse_with_no_name - Sorry, didn't know that. I removed my edit. – Mahmoud Gamal Nov 22 '12 at 07:51
@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. – Jon Skeet Nov 22 '12 at 08:23
@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. – Mahmoud Gamal Nov 22 '12 at 08:56
@MahmoudGamal: No problem - this is SQL we're talking about, after all... it's a foreign language to me :) – Jon Skeet Nov 22 '12 at 09:04
@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. – Jon Skeet Dec 30 '13 at 08:45
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'; – Rahatur Dec 30 '13 at 09:03
@Rahat: What do you mean by "wrong date"? I would expect you to get the same rows, but in a different format. – Jon Skeet Dec 30 '13 at 09:05
@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. – Rahatur Dec 30 '13 at 09:10
@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? – Jon Skeet Dec 30 '13 at 09:13
@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. – Rahatur Dec 30 '13 at 09:21

score 2 · Answer 2 · answered Nov 22 '12 at 07:36

2

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

answered Nov 22 '12 at 07:36

wormhit

3,687
37
46

score 0 · Answer 3 · answered Oct 03 '16 at 11:58

0

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!

answered Oct 03 '16 at 11:58

Gaurav Gupta

478
6
10

mysql date comparison with date_format

3 Answers3

Linked