CODEWITHSUNDEEP
mysql
Jean Fredrik Leirtrø
Jean Fredrik Leirtrø

Reputation: 23

using greater than operator when date is stored not stored in default mysql format

Hi i have a question about using the greater than operator when dates are not stored in the default mysql format. I have a database where i store my dates in dd/mm/yyyy format. I tried to how to store them i proper mysql format, but after a while i gave up on it. Now i have a table with a bunch of dates in dd/mm/yyyy and i want to select the dates that is greater than a selected date

   select * from table where date>'25/05/2015'

when i do that i would get a result like this

       date
    27/05/2015
    28/04/2015

what am i doing wrong? and what can i do to fix it?

Upvotes: 0

Views: 68

Answers (1)

Ed Gibbs
Ed Gibbs

Reputation: 26353

It's best to store dates as a MySQL DATE type for a variety of reasons. The two main reasons are:

  1. You don't have to constantly convert strings to dates for comparisons.
  2. If you have a lot of records and you're searching regularly by date, you can create an index on the date field and you'll get an optimized search. If you index a string field like yours, you'll be optimized for equals/not-equals if you format the date string correctly, but you won't be optimized for ranges or greater-than/less-than because any function call on the field will cause MySQL to ignore the index.

That said, if you're stuck with the current format you can do the comparison by converting the string to a date using the STR_TO_DATE function. Compare the result to a MySQL date literal:

SELECT * FROM table WHERE date > DATE '2015-05-25';

The date literal in this example is the ANSI standard, supported by MySQL and Oracle but not (at least yet) by SQL Server. MySQL can also represent a date literal using any of the following:

  • '2015-05-25'
  • '20150525'
  • 20150525

See here for more information about date and time literals.

Upvotes: 1

Related Questions