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.
18.226.88.110