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

Question

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'

why did you not store those as standard/built-in mysql dates (functions)? rather than what appears to be varchar - https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html - now you've got your work cut out and makes it all that much more harder to query. — Funk Forty Niner, Feb 14 '17 at 15:26
If you're not too far into your project, it's not too late to turn back and I suggest you do. You're making it much harder than it should be. — Funk Forty Niner, Feb 14 '17 at 15:31

score 3 · Accepted Answer · edited Feb 14 '17 at 15:29

3

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.

edited Feb 14 '17 at 15:29

baao

answered Feb 14 '17 at 15:28

Gurwinder Singh

baao · Answer 2 · 2017-02-14T15:41:08.140

1

You should do the following:

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');

Alter table

ALTER TABLE metric MODIFY `dateColumn` DATE;

Use built in mysql date functions for your task

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

edited Feb 14 '17 at 15:41

answered Feb 14 '17 at 15:31

baao

True. I'm suspecting though that they might be getting their data from a datepicker or similar which is formatted that way, which is probably why they're using a possible varchar as a type; not suggested for sure and makes it that much harder to query later on. – Funk Forty Niner Feb 14 '17 at 15:33
1

Yes, I suspect that too - however, one should alter the data from the datepicker. You know how powerful mysql's date functions are @Fred-ii- so I thought I'd better propose their usage. – baao Feb 14 '17 at 15:35
2

Exactly. Like I told them in a comment under their question; it's not too late to change their method if they're not too far into their project. – Funk Forty Niner Feb 14 '17 at 15:36
Having a look at another of their questions http://stackoverflow.com/q/41103730/1415724 which contains `"date=date_format(curdate(), '%d/%m/%Y')"` - it seems that they appear to be too far into their project to alter their db. I often think of "what stands to be ahead of me", whether it's code or "life/every day stuff" related. Oh well, they have enough to keep going I guess. – Funk Forty Niner Feb 14 '17 at 15:43
1

Oh dear... Yeah, that's one of the reasons I started to read the documentation before I develop a project for two years. @Fred-ii- – baao Feb 14 '17 at 15:46

2 Answers2