Filtering Entries by Complex Date
Alright, so I got that table where dates are stored as strings. The reason for this is originally the only thing stored was the year, then the year and month, now the full date. Upstairs says not to change it, so I can't. However, I need to be able to filter all the results by these dates, despite their format.
Now, I can format the dates using:
CASE WHEN needDate IS NULL THEN
'01/01/2012'
ELSE
CASE WHEN CHAR_LENGTH(needDate) > 6 THEN
DATE_FORMAT(STR_TO_DATE(needDate, '%m/%d/%Y'), '%m/%d/%Y')
ELSE
CASE WHEN CHAR_LENGTH(needDate) > 4 THEN
DATE_FORMAT(STR_TO_DATE(needDate, '%Y%m'), '%m/01/%Y')
ELSE
DATE_FORMAT(STR_TO_DATE(needDate, '%Y'), '01/01/%Y')
END
END
END
It works great when displaying all results or results filtered by other columns. The problem is with filtering from this column. Whether the column is included in the display or not, the filter fails to produce results.
Here's what I get when I run the following query:
SELECT needDate, CASE WHEN needDate IS NULL THEN '01/01/2012' ELSE CASE WHEN CHAR_LENGTH(needDate) > 6 THEN DATE_FORMAT(STR_TO_DATE(needDate, '%m/%d/%Y'), '%m/%d/%Y') ELSE CASE WHEN CHAR_LENGTH(needDate) > 4 THEN DATE_FORMAT(STR_TO_DATE(needDate, '%Y%m'), '%m/01/%Y') ELSE DATE_FORMAT(STR_TO_DATE(needDate, '%Y'), '01/01/%Y') END END END AS formatDate FROM projects LIMIT 0,25
02/05/2013 02/05/2013
NULL 01/01/2012
NULL 01/01/2012
NULL 01/01/2012
NULL 01/01/2012
NULL 01/01/2012
02/20/2014 02/20/2014
12/04/2012 12/04/2012
02/10/2014 02/10/2014
01/14/2014 01/14/2014
02/10/2014 02/10/2014
01/21/2014 01/21/2014
01/14/2014 01/14/2014
NULL 01/01/2012
03/11/2014 03/11/2014
201407 07/01/2014
NULL 01/01/2012
03/17/2014 03/17/2014
201405 05/01/2014
NULL 01/01/2012
NULL 01/01/2012
201406 06/01/2014
201407 07/01/2014
NULL 01/01/2012
NULL 01/01/2012
It looks like it translates the entries without issue.
If I try to filter the results by the generated column, I get "#1054 - Unknown column 'formatDate' in 'where clause'", as expected. If I move the CASE WHEN THEN into the WHERE clause, it doesn't error, but it also produces no results.
For example:
WHERE CASE WHEN needDate IS NULL THEN '01/01/2012' ELSE CASE WHEN CHAR_LENGTH(needDate) > 6 THEN DATE_FORMAT(STR_TO_DATE(needDate, '%m/%d/%Y'), '%m/%d/%Y') ELSE CASE WHEN CHAR_LENGTH(needDate) > 4 THEN DATE_FORMAT(STR_TO_DATE(needDate, '%Y%m'), '%m/01/%Y') ELSE DATE_FORMAT(STR_TO_DATE(needDate, '%Y'), '01/01/%Y') END END END BETWEEN DATE_FORMAT('12/31/2011', '%m/%d/%Y') AND DATE_FORMAT('01/02/2012', '%m/%d/%Y')
This should produce a list of only those with a date between that period or NULL (per the CASE WHEN END statement), but I get nothing. I've tried wrapping the CASE WHEN END in a DATE_FORMAT(), wrapping the entire clause in (), and a few other variations without success.
I'm relatively new to using CASE WHEN END, but I know they can be used in the WHERE clause, and for filtering. Anyone know what I'm overlooking?