Delete rows from MySQL table where date field is two weeks or older
<p>I am having to go through someone else's code on a Perl script that updates a MySQL table with any new records and is supposed to also delete from the table any records where the reporting date is two weeks or older.  Currently, the script does not have a date field for the reporting date, but instead is an integer field (not sure why it was done this way).  The Perl script calls a .sql file which is supposed to take care of the delete, but it is not working.</p>

<p>Which would be easier to work with?  Leave it an integer field or change it to a date field and change his sql statement for reflect that?</p>

<p>Here is the current delete statement, if that helps (a bit way too complex for me to tackle yet) - </p>

<pre><code>DELETE FROM Spectros
WHERE
    (spectroReportDt LIKE CONCAT(MONTH(CURDATE()),
        '%',
        DAY(CURDATE()) - 14,
        YEAR(CURDATE()))

        OR spectroReportDt LIKE CONCAT(MONTH(CURDATE()) - 1,
        '%',
        DAY(CURDATE()) + 14,
        YEAR(CURDATE()))

        OR spectroReportDt LIKE CONCAT(MONTH(CURDATE()) + 11,
                '%',
        DAY(CURDATE()),
        YEAR(CURDATE()) - 1))

    AND (CAST(SUBSTR(spectroReportDt, LENGTH(MONTH(CURDATE())) + 1, 2) AS UNSIGNED) &lt;= DAY(CURDATE()) - 14

                OR CAST(SUBSTR(spectroReportDt, 1, LENGTH(MONTH(CURDATE()))) AS UNSIGNED) != MONTH(CURDATE()))
</code></pre>

<p>and the reportDate field is MMDDYYYY in the text file from which it updates and in the MySQL table - except there are no leading zeros on one digit months in the records, since it's an integer.</p>