MySQL Cookbook, 2nd Edition

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-format 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. In MySQL, 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
);

Now, 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')

Applications that use strict SQL mode but require zero parts in dates should be careful not to set the NO_ZERO_IN_DATE SQL mode, which causes the server to consider such dates invalid.