如何在SQL中解析日期？

Question

I have dates stored in a table in a varchar format, like this:

2014-05-29

Year Month Day

So I thought that for using BETWEEN selections, I could get rid of the dashes (20140529) and select between two dates easily like that. For example, between the dates 2014-01-01 and 2014-02-01 would be seen as 20140101 and 20140201, and there is obviously a range of numbers between these that would match an actual date value, for example 20140115.

This is the sql query I plan to select between two dates (in a php file):

$sql = mysql_query("Select * From $table Where Symbol = $symbol 
                And (Concat(Parsename(Replace(Date, '-', '.'), 3), Parsename(Replace(Date, '-', '.'), 2), Parsename(Replace(Date, '-', '.'), 1))
                    Between Concat(Parsename(Replace($lowDate, '-', '.'), 3), Parsename(Replace($lowDate, '-', '.'), 2), Parsename(Replace($lowDate, '-', '.'), 1)) And
                        Concat(Parsename(Replace($highDate, '-', '.'), 3), Parsename(Replace($highDate, '-', '.'), 2), Parsename(Replace($highDate, '-', '.'), 1))))");

So what I'm doing here is getting each index using the parsename function (which gets strings separated by dots, but first replacing the dashes with dots for it to work). It should get, in order, the year, the month and the day, then concatenating them.

By my understanding, it should be doing this with each date; the date data stored in the table, then with the low and high dates (between which I want data) that are stored as variables in php already. Then it should see if the date is between the low and high dates. I'm not sure why this isn't working, any help would be great.

Answer 1

It's not working because PARSENAME is not a MySQL function. If you want to remove the dashes, just use the REPLACE() function to remove the dashes. There's no need for chopping the string up and concatenating it back together.

REPLACE(mycol,'-','')

e.g.

WHERE REPLACE(mycol,'-','') BETWEEN '20140101' AND '20140430'

But since the strings are already in a canonical format, removing the dashes isn't necessary. That is, since the values are all in YYYY-MM-DD format, exactly 10 characters in length with two digit month and day (with leading zeros), then just have your predicate operate on the bare column... just format the other "date" values as strings in the same format, e.g.

mycol BETWEEN '2013-12-01' AND '2014-02-15'

With this form, because the predicate is on a bare VARCHAR column, MySQL should be able to make use of an appropriate index to perform a range scan operation.

To convert your string into an actual MySQL DATE, you could do something like this:

mycol + INTERVAL 0 DAY

You can use that expression in a SQL statement, e.g.

mycol + INTERVAL 0 DAY BETWEEN '2013-12-15' AND '2014-03-31' 

(With this form, because the predicate is operating on an expression, rather than a bare colmn, the MySQL optimizer won't be able to make use of a range scan operation to satisfy the predicate. The expression on the left side of BETWEEN will need to be evaluated for every row (which isn't excluded by some other predicate before this one is evaluated.)

NOTE MySQL provides a DATE datatype which is ideal for storing "date" values. Storing "date" values in VARCHAR is an anti-pattern.