CODEWITHSUNDEEP
mysqlsql
dlofrodloh
dlofrodloh

Reputation: 1744

Selecting date range when date is formatted as dd/mm/YYYY

The dates in my database are stored as dd/mm/YYYY

How can I construct a simple mySQL query to pull dates within a certain range:

e.g.

SELECT * FROM metric WHERE date BETWEEN '31/01/2016' AND '01/02/2017'

Upvotes: 1

Views: 1934

Answers (2)

baao
baao

Reputation: 73231

You should do the following:

  1. Update all the columns that hold a date as a string to a date column

    UPDATE metric SET `dateColumn` = str_to_date(`dateColumn`,'%d/%m/%Y');

  2. Alter table

    ALTER TABLE metric MODIFY `dateColumn` DATE;

  3. Modify your code to insert valid dates to mysql

  4. Use built in mysql date functions for your task

    SELECT * FROM metric WHERE date BETWEEN '2016-01-31' AND '2017-02-01'

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

You can use str_to_date but remember that now the server can't use index on the date column if any.

select *
from metric
where str_to_date(date,'%d/%m/%Y') between '2016-01-31'
        and '2017-02-01'

It's better to store the date as date or if string then in standard format yyyy-mm-dd.

Upvotes: 3

Related Questions