Storing a date where only the year may be known

Question

What's the best way to store a date value for which in many cases only the year may be known?

MySQL allows zeros in date parts unless the NO_ZEROES_IN_DATE sql mode is enabled, which isn't by default. Is there any reason not to use a date field where if the month and day may be zero, or to split it up to 3 different fields for year, month and day (year(4), tinyint, tinyint)?

Answer 1

You can use a single date field in Mysql to do this. In the example below field has the date data type.

mysql> select * from test;
+------------+------+
| field      | id   |
+------------+------+
| 2007-00-00 |    1 | 
+------------+------+
1 row in set (0.00 sec)

mysql> select * from test where YEAR(field) = 2007;
+------------+------+
| field      | id   |
+------------+------+
| 2007-00-00 |    1 | 
+------------+------+

I would use one field it will make the queries easier.

Yes using the Date and Time functions would be better. Thanks BrynJ

Answer 2

A better way is to split the date into 3 fields. Year, Month, Day. This gives you full flexibility for storing, sorting, and searching.

Also, it's pretty trivial to put the fields back together into a real date field when necessary.

Finally, it's portable across DBMS's. I don't think anyone else supports a 0 as a valid part of a date value.

Answer 3

Unless portability across DBMS is important, I would definitely be inclined to use a single date field. If you require even moderately complex date related queries, having your day, month and year values in separate fields will become a chore.

MySQL has a wealth of date related functions - http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html. Use YEAR(yourdatefield) if you want to return just the year value, or the same if you want to include it in your query's WHERE clause.

Answer 4

You could try a LIKE operative. Such as: SELECT * FROM table WHERE date_feield LIKE 2009;

Answer 5

It depends on how you use the resulting data. A simple answer would be to simply store those dates where only the year is known as January 1. This approach is really simple and allows you to aggregate by year using all the standard built in date functions.

The problem arises if the month or date is significant. For example if you are trying to determine the age of a record in days, weeks, months or if you want to show distribution across this smaller level of granularity. This problem exists any way, though. If you have some full dates and some with only a year, how do you want to represent them in such instances.