It’s happened to all of us at some point. You take over a project and some knucklehead who designed the database was too lazy to optimize it. This usually means you will end up with fields named “DateBought” and though it’s supposed to always hold a date and/or datetime value, it’s defined as some VARCHAR 255 character length field. To make matters worse, sometimes the dates are normalized so some dates are entered “2014-05-01” and other times perhaps “05/01/2014”. What do you do? First, fire the person who designed this database, or redesignate them to the graphic design position where maybe they’re better at designing layouts that won’t ruin your business systems and wreak havoc on your reporting.
Here’s a list of handy MySQL time conversions and functions I use a lot: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html
Until then, if you need to rescue… I mean extract the data, you have to convert the text values of the funky fields into time values that are quantifiable. “STR_TO_DATE” is usually the answer to this kind of problem:
1 2 3 | STR_TO_DATE(transdate, '%Y-%m-%d' ) >= '2014-05-01' |
