How to design SQL tables when column data arrives in multiple types/margins of error?

Question

I've been given a stack of data where a particular value has been collected sometimes as a date (YYYY-MM-DD) and sometimes as just a year.

Depending on how you look at it, this is either a variance in type or margin of error.

This is a subprime situation, but I can't afford to recover or discard any data.

What's the optimal (eg. least worst :) ) SQL table design that will accept either form while avoiding monstrous queries and allowing maximum use of database features like constraints and keys*?

*i.e. Entity-Attribute-Value is out.

Answer 1

You could store the year, month and day components in separate columns. That way, you only need to populate the columns for which you have data.

Answer 2

if it comes in as just a year make it default to 01 for month and date, YYYY-01-01

This way you can still use a date/datetime datatype and don't have to worry about invalid dates