If you are used to entering dates in the format DDMMYY, or have existing databases in this format, a quick perl script can migrate these for you. MySQL uses the format of YYYYMMDD. Actually, this makes a lot of sense when sorting left to right. The format for Excel spreadsheets (at least default) is this way, so if you export from a csv file generated in Excel, and import to MySQL, you will have a problem as well. Say we have a file called dt that contains:

07/04/01
02/05/03
4/4/02
5/09/01

All we need is a perl script:

while(<>){
s/(^\d*\/\d*)\/(\d\d)$/20$2\/$1/;
print;
}

If we call the perlscript dc.pl, then:

$perl dc.pl < dt
2001/07/04
2003/02/05
2002/4/4
2001/5/09

Presto, chango, we have dates we can use in MySQL. A little explanation for those of you not used to perl or regular expressions. And for those of you with moderate skills, a refresher may be useful. 🙂 The s at the beginning is the Substitution Operator. The general form of this command is s///. The stuff that matches in the first set of slashes gets replaced with stuff in the second set of slashes. The stuff in the parentheses is put into the variables $1 and $2. So, the parentheses in the first half aren’t actually matched. \\d is a digit. \\/ is a literal / for the date, since you don’t want to make the command think it is the end of the match half of the substitute command. * matches any number of the previous token. so \\d* is any number of digits. ^ matches at the beginning, and $ matches at the end. So, in english, match strings such that the beginning is one or more digits and the end is one or more digits. There will be three sets of digits separated by a /. Put a 20 in front of the last set of digits, and tack on a slash and the other set of digits. Anyway, I hope this helps. I know I usually have to dig out examples each time I write a regular expression like this. Quite powerful. Quite cryptic.