Complete (full) dates and unknown dates can easily be stored in a database table via a nullable date column. What about storing incomplete (partial) dates—where the month, day and/or year is unknown? How do we record July 10 (unknown year) or June 2012 (unknown day)?
MySQL supports incomplete dates out of the box. Simply insert zeros in place of the missing date part:
INSERT INTO TestTable (ID, IncompleteDate) VALUES (NULL, '0000-07-10'), -- unknown year (NULL, '2012-00-15'), -- unknown month (NULL, '2012-06-00'); -- unknown day
Microsoft SQL Server doesn’t support incomplete dates so zeroing out a missing date part won’t work. Let’s examine several ways to work around this limitation.
Suppose we’re storing birth dates. Let’s say we know that a day and month will always be provided but sometimes the year will be omitted. That is, sometimes we’ll need to store dates in the format of March 18 (unknown year).
To use the placeholder year approach, pick a year outside the range of expected year values to use as the unknown year placeholder. In order to accommodate the date February 29 (unknown year), the placeholder year must be a leap year. In the case of our birth date example, let’s choose year 1200 as the unknown placeholder. It’s well outside the range of birth dates used in a normal line of business application and is a leap year. To store March 18 (unknown year), we’ll insert “1200-03-18”.
With this approach, normal SQL date functions work. However, if computations involving the year are invoked, dates using the placeholder year must be filtered out.
-- Failed Attempt: Find individuals born before 1980. -- (Problem: Records with unknown birth years will be included in the result set.) SELECT * FROM TestTable WHERE YEAR(BirthDate) < 1980 -- Successful Attempt: Find individuals born before 1980 SELECT * FROM TestTable WHERE YEAR(BirthDate) > 1200 AND YEAR(BirthDate) < 1980
Date Parts in Separate Columns
To implement, create three columns: day, month, year. If a particular date part may be unknown, allow the associate column to store null values. To insert March 18 (unknown year), we execute:
INSERT INTO TestTable (BirthDay, BirthMonth, BirthYear) VALUES (3, 18, NULL);
This method eliminates the use of a placeholder year and allows for unknown days and months, benefits not offered by the placeholder year approach. Its principle downside? Loss of SQL Server’s built-in date functionality (date functions, conversion/cast operations and date validation checking).
Date validation can be manually recreated using a somewhat complex set of CHECK constraints. The loss of date functions may not be the end of the world. In fact, some operations may become easier. No longer is MONTH(BirthDate) necessary. Selecting BirthMonth now achieves the same effect.
CLR User-Defined Type
In addition to the benefits of the date parts in separate columns approach, a CLR user-defined type (UDT) allows for a degree of cast/conversion operations and stores its data in a single column on the server. With this method, all functionality is packaged into a data type which can easily be reused server-wide. This data type can also be included in client-side programs, allowing those programs to locally work with retrieved data using all the methods and properties defined by the custom data type.
The downside? Out of the methods discussed, this is the most complex to implement