5

I inherited a database that has a VARCHAR column that really should be a DATE. There's no checking on the form (I need to fix that) so dates constantly get entered as 2016-12-4 instead of 2016-12-04 and so forth.

I'm pretty sure there are historically a lot of bad dates, so I'm a little skeevy about just changing the data type to DATE, but I'm curious about what would be a good strategy for changing that?

Background: (I'm a front-end developer, not a DBA, so while I can get around in SQL I don't know what I don't know and I'm always afraid I'll make a mistake that corrupts the database).

4

Use str_to_date() to handle the conversion.

str_to_date('2016-12-4', '%Y-%m-%d'); 
  • That will work even for bad dates? – Robusto Dec 23 '16 at 22:12
  • They're not bad, they're just not 0-padded. – Evan Carroll Dec 23 '16 at 22:13
  • Well, they're bad in that they sort wrong. – Robusto Dec 23 '16 at 22:14
  • 1
    They sort wrong, because they're text, try ORDER BY str_to_date(col, '%Y-%m-%d'); – Evan Carroll Dec 23 '16 at 22:16
  • Ah, thanks! Then given that, do you recommend I change the data type on the column or just leave it as is and use the ORDER BY str_to_date function? – Robusto Dec 23 '16 at 22:26
4

SELECT CAST('2016-12-4' AS DATE); --> 2016-12-04, so you don't need str_to_date.

I would add a new column for the date (ALTER TABLE .. ADD COLUMN ..); UPDATE to set the new column. Then manually fix any really messed up values. Finally DROP COLUMN and RENAME COLUMN;

ORDER BY a VARCHAR that contains a date in it may lead wrong answers. (Jan and Nov may get confused.) Testing for specific dates may fail. (Missing leading 0.)

And indexing the DATE column will make certain queries faster.

1

Changing the VARCHAR column to an DATE column is also not a problem.

Test table and data

CREATE TABLE test.t1( `date_start` VARCHAR(20) ); 
INSERT INTO test.test1 VALUES('2016-1-5');
INSERT INTO test.test1 VALUES('2016-1-5');
INSERT INTO test.test1 VALUES('2016-12-5');
INSERT INTO test.test1 VALUES('2016-12-6');

Select query result.

SELECT * FROM test.test1

date_start  
------------
2016-1-5    
2016-1-5    
2016-12-5   
2016-12-6   

Change the column VARCHAR to DATE

ALTER TABLE test.test1 CHANGE date_start date_start DATE NULL;

Select query result.

SELECT * FROM test.test1 

date_start  
------------
2016-01-05  
2016-01-05  
2016-12-05  
2016-12-06  
  • Did you mean to repeat date_start in the ALTER statement? – Robusto Dec 25 '16 at 18:38
  • 1
    @Robusto yes to change a column's type but not the name, CHANGE syntax still requires an old and new column name, even if they are the same. : – Raymond Nijland Dec 25 '16 at 19:10
  • One thing to note: If the varchar values are not currently in a valid (mysql) format, like 'XXXX-XX-XX', then after the alter statement it will end up with all '0000-00-00'. – Robert Koernke Aug 15 at 14:14
  • I needed to run this command first: update {table} set {field} = DATE_FORMAT(STR_TO_DATE({field}, '%m/%d/%y'),'%Y-%m-%d'); – Robert Koernke Aug 15 at 14:33
  • @RobertKoernke MySQL can directy convert during ALTER from VARCHAR to DATE if the data formats is in a date format MySQLl supports, generally this is in YYYY-MM-DD format.. You had DD-MM-YYYY format then yes you need to use a conversion.. – Raymond Nijland Aug 15 at 15:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.