I stupidly made a row with dates as VARCHAR, can I still do date based selects on it?

Question

So I have a large table that simply cannot be altered without breaking my PHP app. Stupidly (yes I know), I made a start date and end date as VARCHAR with data stored as '03/04/2013' for example.

Now i need to be able to search to see which rows are currently 'active' meaning which rows have a start date before today AND and end date after today.

Is this at all possible with an SQL query?

Possible duplicate of [Parse date in MySQL](http://stackoverflow.com/questions/3296725/parse-date-in-mysql). You just need to do on every query ;-) — Álvaro González, Oct 15 '13 at 17:03
check [STR_TO_DATE()](http://stackoverflow.com/a/3296730/1029621) — Eugen Halca, Oct 15 '13 at 17:04
You can do it, but it will be slow since it can't be indexed. — Barmar, Oct 15 '13 at 17:04
Why not simply convert the column back to datetime type. You can change the type of column. Follow solutions here: http://stackoverflow.com/questions/14611407/how-to-convert-varchar-datetime-to-datetime-type-to-populate-table — Sterling Duchess, Oct 15 '13 at 17:04
@kellax What part of "cannot be altered without breaking my PHP app" was unclear? — Barmar, Oct 15 '13 at 17:04
If you column type can't be changed for any reason, I would then suggest to add 2 date columns and use both from now on, then step by step remove any statement accessing you "old columns", so that at some point you can just drop it... — Laurent S., Oct 15 '13 at 17:08
Only one real and valid solution: Alter the table turning the `varchar` into a `datetime` and then modify the APP accordingly — Mosty Mostacho, Oct 15 '13 at 17:08
I'd consider converting anyway. Your PHP will break, but you can fix it. It's more likely to break even worse in the future if you don't, plus with real date types you'll be able to do fast, indexed searches on start/end dates, something that will probably be important as the database grows. Look at the time spent as penance :) — Ed Gibbs, Oct 15 '13 at 17:08
You could add datetime columns, and implement a trigger that automatically fills them in from the varchar columns after every insert and update. — Barmar, Oct 15 '13 at 17:11

Anthony Accioly · Answer 1 · 2013-10-15T18:38:04.270

Action plan to migrate VARCHAR columns to DATE without breaking the application:

Create new indexed DATE columns and fill them with the respective values in the VARCHAR columns:

-- new column
ALTER TABLE MY_TABLE ADD `NEW_DATE_COLUMN` DATE;    
-- index
CREATE INDEX `MY_TABLE_NEW_DATE_IDX` ON MY_TABLE(`NEW_DATE_COLUMN`);
-- initial values
UPDATE MY_TABLE 
SET `NEW_DATE_COLUMN` = STR_TO_DATE(`VARCHAR_DATE`, '%d/%m/%Y')
WHERE `NEW_DATE_COLUMN` IS NULL;

Create insert / update triggers to cast your VARCHAR columns to DATE and update your new DATE columns with their respective values:

-- triggers
DELIMITER //
CREATE TRIGGER `MY_TABLE_VARCHAR_DATE_BI` BEFORE INSERT ON MY_TABLE
  FOR EACH ROW
  BEGIN
    IF NEW.`NEW_DATE_COLUMN` IS NULL AND NEW.`VARCHAR_DATE` IS NOT NULL THEN
      SET NEW.NEW_DATE_COLUMN = STR_TO_DATE(NEW.`VARCHAR_DATE`, '%d/%m/%Y');
    END IF;
  END;
//
CREATE TRIGGER `MY_TABLE_VARCHAR_DATE_BU` BEFORE UPDATE ON MY_TABLE
  FOR EACH ROW
  BEGIN
    IF NEW.`NEW_DATE_COLUMN` IS NULL AND NEW.`VARCHAR_DATE` IS NOT NULL THEN
      SET NEW.NEW_DATE_COLUMN = STR_TO_DATE(NEW.`VARCHAR_DATE`, '%d/%m/%Y');
    END IF;
  END;
//
DELIMITER;

Use the DATE columns in your queries:

SELECT * FROM MY_TABLE
WHERE `NEW_DATE_COLUMN` BETWEEN 
CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 1 DAY);

Take your time and update your application to get ride of places that uses the original VARCHAR columns directly, meanwhile nothing will be broken.

When you are done remove the triggers and the VARCHAR columns:

DROP TRIGGER `MY_TABLE_VARCHAR_DATE_BI`;
DROP TRIGGER `MY_TABLE_VARCHAR_DATE_BU`;

ALTER TABLE MY_TABLE DROP `VARCHAR_DATE`;

Working SQL Fiddle.

score 2 · Answer 2 · answered Oct 15 '13 at 17:03

2

Yes you can do that.

Try something like this:-

select date_format(str_to_date('03/04/2013', '%d/%m/%Y'), '%Y%m');

or may be this:-(Just a small change with month and days as I am confused with 03 and 04)

select date_format(str_to_date('03/04/2013', '%m/%d/%Y'), '%Y%m');

OR you may also try to convert your column back to date like this:

UPDATE `table`
SET `column` = str_to_date( `column`, '%d-%m-%Y' );

answered Oct 15 '13 at 17:03

Rahul Tripathi

168,305
31
280
331

2

It's worth mentioning that this will result not only in a loss of performance due to the fact that you have to convert the content of the column but also because you won't allow MySQL to use any potential index in the column – Mosty Mostacho Oct 15 '13 at 17:12

Mihai · Answer 3 · 2013-10-15T17:12:55.890

0

UPDATE `table`
SET `yourColumn` = str_to_date( `yourColumn`, '%d-%m-%Y' );

Convert it do date type without losing your data,add minutes or seconds as needed it.IT will be easier in the long run,but if you prefer dabbling in php..

Or create a new column date type from the varchar one.

edited Oct 15 '13 at 17:12

answered Oct 15 '13 at 17:07

Mihai

26,325
7
66
81

This will break his PHP application, which expects the column to be in `mm/dd/yyyy` format. – Barmar Oct 15 '13 at 17:09

score 0 · Accepted Answer · answered Oct 15 '13 at 17:09

0

Use STR_TO_DATE as follows:

WHERE STR_TO_DATE(start, '%d/%m/%Y') < DATE(NOW())
  AND STR_TO_DATE(end, '%d/%m/%Y') > DATE_ADD(DATE(NOW()), INTERVAL 1 DAY)

answered Oct 15 '13 at 17:09

Barmar

741,623
53
500
612

1

It's worth mentioning that this will result not only in a loss of performance due to the fact that you have to convert the content of the column but also because you won't allow MySQL to use any potential index in the column – Mosty Mostacho Oct 15 '13 at 17:13
I did mention that -- in a comment above. – Barmar Oct 15 '13 at 17:17
@Mark, while Barmar answer is technically correct and will get "things done"... As mentioned by him and several other people, it is extremely inefficient and will hurt your application in the long run. I took the time to compile a very detailed answer with how to tackle the problem without breaking your PHP code. Please take a look at it (and the rest of the stackoverflow community, please follow the same advice hehehe). – Anthony Accioly Oct 15 '13 at 18:19
@AnthonyAccioly Thank you so much! You are correct! I will in the long term be migrating my entire application following your advice, however the application is very clunky and large, so for the immediate future Barmar's answer works for now and gets the 'check' But your answer is ultimatly the way to go and what I will do over the course of the next month. Thanks!! – Mark Oct 19 '13 at 16:18

I stupidly made a row with dates as VARCHAR, can I still do date based selects on it?

4 Answers4