MySql date inside varchar (select correct date)?

Question

I have "varchar" field in my database where I have stored records like:

(11.1.2015) Log info 1
(17.4.2015) Log info 2
(22.5.2015) Log info 3
(25.5.2015) Log info 3
...

Now I would like to make SELECT WHERE date inside () is the same or larger than today and select the first one (so in this sample and todays date I should get 22.5.205). I just can't figure out how to do that, so I need some help.

This is going to be awfully complex to do with a varchar field, if not impossible. This calls for proper [normalization.](http://stackoverflow.com/questions/1258743/normalization-in-mysql) It's painful and makes some queries more complex, but cases like this become much easier and faster. — Pekka, May 07 '15 at 06:17
In PHP I get the date with preg_match('#$(.*?)$#', $data, $dateinside);", but I don't know how to get it in sql call... — SilverSilencer, May 07 '15 at 06:17
A preg_match is easy but you want to test for a date range. Not sure whether that is even possible. You'd be much better of normalizing your database, check the link I added above — Pekka, May 07 '15 at 06:19
Am I missing something? This is tedious but quite easy nonetheless!!!!?!? See STR_TO_DATE() — Strawberry, May 07 '15 at 06:31

score 1 · Answer 1 · edited May 23 '17 at 12:21

In principle I agree with Pekka웃 on this one.
You should always strive to use proper data types for your data.
This also means never use one column to store 2 different data segments.

However, from the comments to Pekka웃's answer I understand that changing the table is not possible, so here's my attempt to do it.
Assuming your dates are always at the start of the varchar, and always surrounded by parenthesis, you can probably do something like this:

SELECT *
FROM (
    SELECT 
           CAST(SUBSTR(Log_data, 2, LOCATE(')', Log_data)-1) as date) LogDate,
           SUBSTR(Log_data, LOCATE(')', Log_data)+1,  CHAR_LENGTH(Log_data)) LogData
    FROM logs_table
) NormalizedLogTable
WHERE LogDate >= CURDATE()
Limit 1

See sql fiddle here.

Note #1: This is a workaround for your specific situation.
If you ever get the chance, you should normalize your table.

Note #2 I'm not a MySql guy. Most of my sql experience is with Sql server.
You can probably find a better way to convert strings to date then just using cast, to overcome the ambiguity of values like 1.3.2015.

score 0 · Answer 2 · edited May 23 '17 at 11:51

This is likely to be impossible to do with a varchar field, or hellishly complex. While you can theoretically use Regex functions in mySQL to match patterns, you are looking for a date range. Even if it were possible to build a query somehow, it would be a gigantic pile of work and there is no way mySQL could optimize for any aspect of it.

The normal, fast, and straightforward way to go about this is normalizing your table.

In your case, you would probably create a new table named "logs" and connect it to your existing table through an ID field that shows which parent record each log entry belongs to.

Querying for a certain date range for log entries belonging to a specific parent then become as easy as

SELECT log_date FROM logs WHERE parent = 155 AND log_date >= CURDATE()

It's painful to do at first (as you have to rebuild parts of your structure and likely, your app) and makes some everyday queries more complex, but cases like this become much easier and faster.

Unfortunately I can't edit tables (If I could I would), so I am stuck with using this. I guess I will select all then and check dates in PHP. — SilverSilencer, May 07 '15 at 06:28
@SilverSilencer that indeed seems like the only way to go then. MySQL probably doesn't have the tools to do it. — Pekka, May 07 '15 at 06:29

MySql date inside varchar (select correct date)?

2 Answers2