Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Storing Imprecise Dates

Status
Not open for further replies.

samson99

Programmer
Nov 1, 2005
1
GB
We need to store some date information in our database that has been recorded over the last 50 years. Although for approx the last 30 years, events have been recorded to the nearest day, prior to that we may only have the correct month or, in some cases, the year.

How should I store this information in my database so that:
i. It can still be used for computations, orderings etc.
ii. We don't assign an undeserved level of accuracy to old data - I really don't want to pretend that an event happened on 1st Jan 1958 if all I know is that it happened in 1958.
iii. We can display the appropriate level of accuracy when retrieving date information - ie. if all we know is that the year was 1958 then this is what all that we should display.

I suspect that I need to use two columns, one storing a date and a second highlighting the level of accuracy of that date (YEAR / MONTH / FULL). Any input as to just how we might arrange this would be most welcome.

John
 
well, you cannot store just 1958 into a date or datetime column, except in mysql, which does accommodate imprecise dates (not sure if they've restricted this in version 5.0) so you can enter 1958-00-00 or 1958-09-00

suggest you use a SMALLINT for year and two TINYINTs for month and day, allowing the latter to be NULL (or zero, if you like to live dangerously)

then for date comparisons, you could assemble these three columns as a date value within the query, allowing nulls to propagate so that imprecise dates are not matched

on the other hand, you could also write queries to test inclusion, e.g. where year(somedate) = event_year

r937.com | rudy.ca
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top