CODEWITHSUNDEEP
sqldate
lightxx
lightxx

Reputation: 1067

sql store date when just the decade / century is known

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?

Upvotes: 6

Views: 1365

Answers (6)

MatBailie
MatBailie

Reputation: 86716

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.

Upvotes: 3

Alaa
Alaa

Reputation: 575

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)

Upvotes: 0

Agent_L
Agent_L

Reputation: 5411

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.

Upvotes: 5

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

If you have no need to store time with date then use datatype "Date", no need to go for datetime2 to just allowing date from 01-01-0001.

Date also support dates from 0001-01-01 through 9999-12-31. Datetime2 has more time accuracy than datetime.

Upvotes: 1

Philip Fourie
Philip Fourie

Reputation: 116857

Interesting question, I would consider the following solution:

Save the values as two fields on the database. The first are stored in the format as you mentioned '1500-01-01 00:00' for sorting purposes. The second field are used to record the original value 15XX, its data type is of an alphanumeric type.

With this approach you are not losing the fact that the data is unknown. But you still you meet your requirement of searching for books older than a certain date.

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

Upvotes: 1

Manuel
Manuel

Reputation: 10303

In my opinion there are 3 ways of saving the date of such books:

  • 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)
  • halfway the above (for 15xx it would be 1550)

These approaches are irrelevant for the code itself, but they will influence your results when you query for a certain age. So whatever feels best for you should be fine in my opinion.

In other words when you query for a book of 500 years old would you want to get a book that is from 15xx or not? As it is the year 2012 right now the book will not be returned by the database (2012 - 500 = 1512).

Upvotes: 1

Related Questions