There are three different methods that I have thought to do. Another method which is the same way how MySQL implements dates where they use 0s for unknown date parts.
The first attempt was to create multiple columns. This worked ok until I had tables that required multiple date fields then they became cumbersome although could still work.
The next attempt was to use the date field and a bitmap column to identify which part is unknown. This seems cleaner.
I then attempted to research if there was a way to create a new type. I started looking into the user defined types but this did not appear to do what I needed it to do.
The last idea (not yet implemented) is to create another table to store partial dates, then use an id to create a link. This way I did not have to create multiple columns. I am not sure whether I will ever attempt this. The above two seem like a cleaner way to do the same thing.
I had also thought to use an int column to store the date with 0 for unknown parts, YYYYMMDD. For example, 101 will be January, 1st with unknown year; while 20150100 will be January 2015.
Different scenarios where I had the need for partial dates:
- Birthdays - Users may or may not provide all the information. Year could be required for certain applications. For social media, users are less likely to provide year. Month or day could be the only information known from conversations without prying further.
- Founded dates - Certain situations like publishers only give the month and year. Other times only the year is known.
- Known dates - When tracking historical information from hearsay, only confident information can be used. For example when determining the start date of an employee, a person can recall that they started in a certain month but not the exact date.
