Sql store when only a decade / century is known

Question

I have a list of books that I want to store in the database. one attribute is the date the book was first published. from older books (over 100 years old) I often know a decade (e.g. 185X) or in the case of very old books of the entire century (e.g. 15XX).

How do you save these dates in the datetime2 field? 15XX how is 1500? For example, I want to be able to request books that are over a hundred years old. so I somehow want to keep these values ​​as a valid datetime2 value. any recommendations? 15XX as "1500-01-01 00:00" seems reasonable to me. any flaws in this approach?

Answer 1

The only drawback is that someone asks for all the books published from 1550 to 1650. Your 15XX has become 1500, so it will not be included in its results.

You really have a period of uncertainty when this book was published. I would save 2 dates: one when the period began, and the other when it ended. In modern books, it will be set to the same dates, but the oldest can be saved as 1500-01-01 00:00 - 1599-12-31 23:59

Of course, this will complicate the choice. You have to decide if it's worth it. You can say that asking "from 1550 to 1650" is simply stupid.

Answer 2

In @ dragon112's answer, is it likely that you will need 15XX as BOTH of the first two parameters? (Just like NULL is and is not any value at the same time.)

• the oldest date for this book (for 15xx it will be 1500).
• the youngest possible date for this book (for 15xx it will be 1599).

If so, you can save the two dates and specify the date range within which the book was published.

This makes your queries / system more complex. When writing an SQL bot, they are syntactically correct, but you need to choose what is suitable in any given situation, since they can give different results ...

 WHERE earliestPublishDate > '1550-01-01' WHERE latestPublishDate > '1550-01-01' 

So, the most important question when deciding how to store your data:
“How are you going to interrogate him?”

You need to know your use cases (or likely use cases) to determine the correct presentation of the data.

Answer 3

In my opinion, there are 3 ways to save the date of such books:

• the oldest date for this book (for 15xx it will be 1500).
• the youngest possible date for this book (for 15xx it will be 1599).
• halfway up (for 15xx it will be 1550).

These approaches do not matter for the code itself, but they will affect your results when asked for a specific age. So, whatever is best for you, it should be good in my opinion.

In other words, when you request a book in 500 years, do you want to get a book written with 15xx or not? As in 2012, the book will not be returned by the database (2012 - 500 = 1512).

Answer 4

An interesting question, I would consider the following solution:

Save the values ​​as two fields in the database. The former are stored in the format, as you mentioned "1500-01-01 00:00" for sorting. The second field is used to record the initial value 15XX, its data type is alphanumeric.

With this approach, you do not lose the fact that the data is unknown. But you still fulfill your requirement of finding books older than a certain date.

The date time field is then strictly calculated from the alphanumeric field.

Answer 5

If you do not need to store time with a date, use the data type "Date", you do not need to go to datetime2 to specify a date from 01-01-0001.

The date also supports dates from 0001-01-01 to 9999-12-31. Datetime2 has more time than datetime.

Answer 6

 DECLARE @var VARCHAR(100) SET @var = '' SET @var = CASE LEN(@var) WHEN 1 THEN @var + '000' WHEN 2 THEN @var + '00' WHEN 3 THEN @var + '0' ELSE @var END SELECT CAST(@var AS DATE)