Recipe 10.29. Converting Two-Digit Year Values to Four-Digit Form

Problem

You need to convert years in date values from two digits to four digits.

Solution

Let MySQL do this for you, or perform the operation yourself if MySQL's conversion rules aren't appropriate.

Discussion

Two-digit year values are a problem because the century is not explicit in the data values. If you know the range of years spanned by your input, you can add the century without ambiguity. Otherwise, you can only guess. For example, the date 2/10/69 probably would be interpreted by most people in the U.S. as February 10, 1969. But if it represents Mahatma Gandhi's birth date, the year is actually 1869.

One way to convert years to four digits is to let MySQL do it. If you store a date containing a two-digit year, MySQL automatically converts it to four-digit form. MySQL uses a transition point of 1970; it interprets values from 00 to 69 as the years 2000 to 2069, and values from 70 to 99 as the years 1970 to 1999. These rules are appropriate for year values in the range from 1970 to 2069. If your values lie outside this range, you should add the proper century yourself before storing them into MySQL.

To use a different transition point, convert years to four-digit form yourself. Here's a general-purpose routine that converts two-digit years to four digits and allows an arbitrary transition point:

sub yy_to_ccyy { my ($year, $transition_point) = @_;   $transition_point = 70 unless defined ($transition_point);   $year += ($year >= $transition_point ? 1900 : 2000) if $year < 100;   return ($year); } 

The function uses MySQL's transition point (70) by default. An optional second argument may be given to provide a different transition point. yy_to_ccyy⁠(⁠ ⁠ ⁠) also makes sure the year actually needs converting (is less than 100) before modifying it. That way you can pass year values that do or don't include the century without checking first. Some sample invocations using the default transition point have the following results:

$val = yy_to_ccyy (60);         # returns 2060 $val = yy_to_ccyy (1960);       # returns 1960 (no conversion done) 

But suppose that you want to convert year values as follows, using a transition point of 50:

00 .. 49 -> 2000 .. 2049 50 .. 99 -> 1950 .. 1999 

To do this, pass an explicit transition point argument to yy_to_ccyy⁠(⁠ ⁠ ⁠):

$val = yy_to_ccyy (60, 50);     # returns 1960 $val = yy_to_ccyy (1960, 50);   # returns 1960 (no conversion done) 

The yy_to_ccyy⁠(⁠ ⁠ ⁠) function is one of those included in the Cookbook_Utils.pm library file.