Storing Incomplete (Partial) Dates in SQL Databases

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.

Placeholder Year

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

 

2 thoughts on “Storing Incomplete (Partial) Dates in SQL Databases

  1. Anubhuti David

    What would be your suggestion for Place holder Month and Day?

    The Placeholder for year works like a charm, but I need help for Month and Day.

    If I use 13 as the month, I cant convert it to Date/DateTime. Same for day, I tried using 32 but it does help 🙁

    Reply
    1. Ben

      If you’re using MySQL server, try using 00 as the placeholder. With Microsoft SQL Server, you’ll probably have to use the Date Parts in Separate Columns approach described above (unless you want to go to the effort of a CLR User-Defined Type).

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *