Using Dates with Missing Components
Problem
The dates in your data are incomplete, that is, they have missing subparts.
Solution
MySQL can represent them as ISO dates using zero for the missing parts.
Discussion
Some applications use dates that are not complete. For example, you
may need to work with input values such as
Mar/2001 that contain only a month and year. As of
MySQL 3.23, it’s possible to represent such values
as ISO-format dates that have zero in the
“missing” parts. (The value
Mar/2001 can be stored as
2001-03-00.) To convert month/year values to ISO
format for import into MySQL, set up a hash to map month names to
their numeric values:
my %map = # map 3-char month abbreviations to numeric month
(
"jan" => 1, "feb" => 2, "mar" => 3, "apr" => 4, "may" => 5, "jun" => 6,
"jul" => 7, "aug" => 8, "sep" => 9, "oct" => 10, "nov" => 11, "dec" => 12
);Then convert each input value like this:
if ($val =~ /^([a-z]{3})\/(\d{4})$/i)
{
my ($m, $y) = (lc ($1), $2); # use lowercase month name
$val = sprintf ("%04d-%02d-00", $y, $map{$m})
}After storing the resulting values into MySQL, you can retrieve them
for display in the original month/year format by issuing a
SELECT statement that rewrites the dates using a
DATE_FORMAT( ) expression:
DATE_FORMAT(date_val,'%b/%Y')