mysql date comparison with date_format

by

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

More Related Contents:

  1. Left Outer Join doesn’t return all rows from my left table?
  2. Get a list of dates between two dates
  3. How to populate a table with a range of dates?
  4. MySQL Query GROUP BY day / month / year
  5. Error in MySQL when setting default value for DATE or DATETIME
  6. How to get the number of days of difference between two dates on MySQL?
  7. Mysql: Select all data between two dates
  8. Difference between two dates in MySQL
  9. How to get difference between two dates in months using MySQL query?
  10. Select mysql query between date?
  11. How do I select between the 1st day of the current month and current day in MySQL?
  12. Parse date in MySQL
  13. Get the latest date from grouped MySQL data
  14. How to get an age from a D.O.B field in MySQL?
  15. mysql: get record count between two date-time
  16. How to get first day of every corresponding month in mysql?
  17. Count days between two dates, excluding weekends (MySQL only)
  18. How to cast DATETIME as a DATE in mysql?
  19. How do I get the first day of the week of a date in mysql?
  20. MySQL: How to select records for this week?
  21. Compare only day and month with date field in mysql
  22. MySQL – select data from database between two dates
  23. How to display a date as iso 8601 format with PHP
  24. How to select only date from a DATETIME field in MySQL?
  25. Select all months within given date span, including the ones with 0 values
  26. Insert/ Update random date in MySQL
  27. How to compare timestamp dates with date-only parameter in MySQL?
  28. SQL date format convert? [dd.mm.yy to YYYY-MM-DD]
  29. MySQL: Find Missing Dates Between a Date Range
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment