sql store date when just the decade / century is known

Question

i have a list of books i want to store in a database. one of the attributes is the date when the book was first published. of the older books (older than 100 years) i often just know the decade (like 185X) or in case of the very old books just the century (like 15XX).

how would you save those dates in a datetime2 field? 15XX as 1500? i want to be able to query for books which are older than a hundred years, for example. so i somehow want to store those values as a valid datetime2 value. any recommendations? 15XX as '1500-01-01 00:00' seems reasonable to me. any drawbacks of that approach?

Answer 1

The only drawback is when someone asks for all books published from 1550 to 1650. Your 15XX became 1500, so it won't be included in his results.

What you really have is a period of uncertainty when given book was published. I'd store 2 dates: one when the period started, and the other when ended. Modern books will have it set to same dates, but the oldest ones can be stored as 1500-01-01 00:00 - 1599-12-31 23:59

Of course it will complicate selects. You have to decide if it's worth it. You may declare that asking for "1550 to 1650" is plain stupid.

Answer 2

In extension to @dragon112's answer, is there the possibility that you would need 15XX as BOTH of the first two options? (In the same way as NULL is and isn't any value, at the same time.)

• the oldest possible date for that book (for 15xx it would be 1500)
• the youngest possible date for that book (for 15xx it would be 1599)

If so, you could store two dates and make a date range within which the book was published.

This does make your queries/system more complex. When writing the SQL bot of these are syntactically correct, but you'd need to pick which is appropriate in any given situation, as they could give different results...

WHERE
  earliestPublishDate > '1550-01-01'

WHERE
  latestPublishDate > '1550-01-01'

So, the most important question when determining how to store your data:
- How are you going to interrogate it?

You need to know your use-cases (or likely use cases) in order to determine your correct data representation.