How to parse dates in 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.

Don't do this! If you want to compare dates use the MySQL str_to_date() function to create a date and use that. Your dates shouldn't be stored as varchar() anyway - they should be stored as date or datetime and all this malarkey would be irrelevant. — user1864610
– user1864610, Commented May 29, 2014 at 2:52

spencer7593 · Accepted Answer · 2014-05-29 20:41:14Z

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.

user3575290 · Accepted Answer · 2014-05-29 03:26:48Z

0

Use the DATE format in mysql. if YYYY-MM-DD format is there, you can simply use $date3 => Initial date $date4 => Final Date

query="SELECT * FROM table where date1 >='$date3' AND date2 <='$date4'";

Provided that the date1 and date2 fields in mySql are defined as date type.

answered May 29, 2014 at 3:26

user3575290

113 bronze badges

Comments

user149341 · Accepted Answer · 2014-05-29 03:27:12Z

While my best advice would be to convert your application to store dates at the MySQL DATE type, there is a simple workaround for your current situation.

When represented in the form YYYY-MM-DD like you have them here, dates are already ordered properly as strings. (That is, given two dates A and B, the string A will always compare less than the string B if and only if the date A is before the date B.) Thus, you don't need to do any conversion at all here — simply checking whether the Date column is between the strings $lowDate and $highDate is sufficient.

I'm not totally happy with the PHP code you've currently got (the mysql extension is deprecated, and I'm a bit worried about potential SQL injection), but, using what you've provided as an example, this query might be written more simply as:

SELECT * FROM $table WHERE Symbol = $symbol AND Date BETWEEN '$lowDate' and '$highDate'

Also, worth noting: This same code will also work if you convert to using DATE internally. It'll just start working even better.

Collectives™ on Stack Overflow

How to parse dates in SQL?

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related