Even if I allow null values and replace all the NV instances with ""(nothing), I still get zero in that cell.

I suspect that this isn't doing what you think it is. Where MySql is concerened, "" is not the same as NULL.

Consider the following example:

mysql> create table foo (Year year(2) default null);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into foo values ("");
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+------+
| Year |
+------+
|   00 |
+------+
1 row in set (0.00 sec)

mysql> insert into foo values (NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from foo;
+------+
| Year |
+------+
|   00 |
| NULL |
+------+
2 rows in set (0.01 sec)
[download]

If you want to represent your NV values with NULL, then you need to explicity specify NULL when you insert. (If you are using placeholders, then I think an undef value will evaluate to NULL, but you'd have to check that).

Update: Actually (and before somebody jumps on me), that statement above isn't quite true. eg:

mysql> insert into foo values ();
Query OK, 1 row affected (0.00 sec)

mysql> select * from foo;
+------+
| Year |
+------+
|   00 |
| NULL |
| NULL |
+------+
[download]

Hope this helps,
Darren :)

If you are using placeholders, then I think an undef value will evaluate to NULL, but you'd have to check that

Yep, it does. And that's what he should use.

Is isn't clear from your "question" where the problem might be, but there are a few things you can try...

Make sure you quote the year field (eg, '02') as MySQL will helpfully strip the leading zeros off bare numbers (eg, 02).

If you're INSERTing this data using DBI and placeholders, it's possible DBI is misquoting your input.

Using bind_params may help to coerce it otherwise, or you could explicitly pad your input instead so that the absence of quotes on numbers becomes a non-issue (eg, INSERT ... LPAD(?, 2, 0)).

Padding works equally well if you prefer to tackle this on the output side of things, and can be done in your SQL as above (eg, SELECT ... LPAD(...)) or by massaging the results in Perl afterwards (eg, sprintf '%02s', $year_col, but note the use of 's' instead of the more common 'd').

The MySQL YEAR data type supports the concept of a zero year to represent invalid years. This is distinct from the null value which you can also assign. You may want to use either one of those two values to represent NV (No Vintage). Or as xdg recently showed me, you may not want to.

In any case you are encountering problems because you are truncating the field to 2 digits. As you can see in the MySQL documentation for the YEAR type, the rules for interpreting truncated years are a little complicated. Why don't you just specify the year as a numeric value in the allowed range of the YEAR type, which is 1901 through 2155, and don't truncate things to two digits. The 0 invalid year will not give you trouble then.

Another option might be to store a BOOL flag for NV. This isn't as efficient, however, since BOOL is generally stored as a byte. Might as well TINYINT and save space.