CODEWITHSUNDEEP
mysqldatecastingvarchar
Andri
Andri

Reputation: 461

How do I compare dates (varchar) formatted as dd.mm.yyyy in MySQL?

I'd like to be able to select all dates between two dates in a MySQL database where the dates are a varchar in the format dd.mm.yyyy.

Database
date (varchar) | value
------------------------
31.01.2018     | 123
------------------------
28.02.2018     | 456

This does not work

Query
SELECT date,value from Database WHERE date BETWEEN '2018-01-01' AND '2018-01-31'

I know I could use WHERE date LIKE '%.01.2018' for whole months but that is not sufficient.

I could do this in PHP, but don't want to do that, as that will take too long. I guess I could possibly use the CAST function, but not sure how.

What is the best way to do this in MySQL?

Upvotes: 2

Views: 3938

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562631

The best way to do this in MySQL is to avoid storing dates as a string in DD.MM.YYYY format. Instead, store dates in proper DATE columns, so they match the native format: YYYY-MM-DD.

The solutions of using STR_TO_DATE() or other functions means you can't use an index to optimize comparisons. The search will run with poor performance, because it has to do a table-scan.

Re your comment:

2 million rows is not a large table. It shouldn't take more than a minute or two to restructure it. It will only get harder if you wait until the table gets larger.

Since your data is in the wrong format, you can't just change the data type. Here's how I would fix this:

Step 1: Add a new column that is a proper DATE data type:

ALTER TABLE MyTable ADD COLUMN myDate DATE;

(By the way, don't name your table "Database" or your column "date". Those are reserved words, and not descriptive anyway. Do you name program variables "variable"?)

Step 2: Create a trigger so your app's use of the old column is automatically copied to the new column:

CREATE TRIGGER InsMyDate BEFORE INSERT ON MyTable
FOR EACH ROW 
SET NEW.myDate = COALESCE(NEW.myDate, STR_TO_DATE(NEW.oldDate, '%d.%m.%Y'));

CREATE TRIGGER UpdMyDate BEFORE UPDATE ON MyTable
FOR EACH ROW 
SET NEW.myDate = COALESCE(NEW.myDate, STR_TO_DATE(NEW.oldDate, '%d.%m.%Y'));

The reason for the COALESCE will be clear in a moment.

Step 3: Backfill all the values in past rows:

UPDATE MyTable
SET myDate = STR_TO_DATE(NEW.oldDate, '%d.%m.%Y')
WHERE myDate IS NULL;

You only need to do this once. Any new rows inserted will automatically be copying the proper date value to the new column after you create the triggers. Any rows inserted before you created the triggers will have NULL in the new column, so you need to UPDATE them as shown above.

Step 4: Change your application code so any reference to the old wrong date column uses the new date column. Address any INSERT, UPDATE, or SELECT that references the date. Test and deploy this code change.

Once you do this, any INSERT or UPDATE will specify a value in the new date column instead of a NULL. That will make the trigger effectively a no-op, because a non-NULL value in myDate will take precedence over the STR_TO_DATE() expression. This allows you to deploy the code at your convenience, and the database will just continue to do the right thing with your data.

Step 5: Drop the triggers and the old string-date column:

DROP TRIGGER InsMyDate;
DROP TRIGGER UpdMyDate;
ALTER TABLE MyTable DROP COLUMN oldDate;

If you're worried that each ALTER TABLE will lock the table during the table-restructuring, you should learn to use pt-online-schema-change. This allows you to do an ALTER TABLE without locking (except for a brief moment at the end of the restructuring).

Upvotes: 5

Lajos Arpad
Lajos Arpad

Reputation: 76757

You can choose revo's solution, or, alternatively this one

SELECT date,value from Database WHERE (not (date is null)) and CHAR_LENGTH(date) = 10 (CONCAT(SUBSTRING(date, 7), '-', SUBSTRING(date, 4, 2), '-', SUBSTRING(date, 1, 2))) BETWEEN '2018-01-01' AND '2018-01-31'

Here, we check for date being not null and being of the required length, so your program will not crash when it meets unconventional values.

Upvotes: 0

revo
revo

Reputation: 48751

You could use STR_TO_DATE():

WHERE STR_TO_DATE(date, '%d.%m.%Y') BETWEEN '2018-01-01' AND '2018-01-31'

Upvotes: 3

Related Questions