Splitting fields in sql query and sorting by them

Question

I have a field containg a combined date, something line 2012-02-03 - 2012-02-05 where the first date is the "from" date, and second is the "to" date.

Is there a way that I can split these two dates and then order by the "from" date ?

I know the best thing would be to have two different fields for these two dates, but since I began doing it this way there is alot of recoding to seperate them.

Answer 1

You can use SUBSTRING_INDEX for this.

Assuming your dates have ' - ' between them (i.e. space, hypen, space), you can do something like:

SELECT SUBSTRING_INDEX(datefield,' - ',1) as fromDate,
       SUBSTRING_INDEX(datefield,' - ',-1) as toDate,
FROM ..
ORDER BY DATE(fromDate)

SUBSTRING_INDEX(string,delimiter,count) returns all of string up to (not including) the countth occurence of delimiter. If delimiter is negative it counts from the right and returns the substring from the end of the string instead.

I used DATE(fromDate) to convert fromDate from a string to a MySQL Date so it sorts in a date-like fashion.