SQL Date pattern for dates that user may not know all the information

Question

You system allows new users to register and enter their wedding date. Being a purist, I wanted to store this as a date. After user interviews, it was clear that the reality differs from this approach. Users are often not sure of the day or the month, and wish to store partial dates, so "June 2009" or "sometime in 2012".

Does anyone know a good design pattern that allows the storing partial dates, and then trying to create a timeline for the big day - even though there are partial dates entered?

Answer 1

You can always store the date in separate columns:

• year
• month
• day-of-month

Keep them all nullable. You can add a column where you store the computed full time. For that computed time you use some default values for all the columns that are null.

Answer 2

You could store it as a range with an after-date and before-date column.

Answer 3

Maybe a "fuzzy" date. If they say "June 2009", that could be stored as 15 June 2009, with a variance of +/- 15 days.

"sometime in 2010" would be 01 July 2010 with a variance of 180 days.

So store a date and a # days.

If/how that would work in practice I don't know. Just an idea. A date range might be easier to implement.

Answer 4

You could store dates as YYYYMMDD, with unknown months/days set to 0. e.g., 20090200 would be "some day in feb 2009". This will all you to sort and compare relatively easily, and if you need to convert it to a date, you can just add 1 to the month or day to get an approximation for more advanced date comparisions.

Answer 5

I had a similar problem with dates; with the added issue that some people also wanted to say "15 June", without specifying the year. Is this a case you have to handle too?

I stored the day, month and year in three separate columns in the database, with each nullable, then (somewhat) re-invented the wheel in my application (as well as disallowing some cases, such as people saying "The 15th of 1974").

However, as I put all the date-handling logic in my application, I never accessed the three columns individually. With hindsight I wished I had put them in a single column, holding partial dates as strings (something like "19740615", "1974----", "----0615", etc.), just so that I'd have less column clutter.

I guess it depends how much you want to do in the database and how much you want to do in your application. Either way, I think you'll be inventing a slightly differently-shaped wheel. But of course, you're good enough to make some tidy, encapsulated, reusable classes - that'll make it fun! :-)