How to store an incomplete date?
When doing apps that deals with date there almost always comes a question on
how to store an incomplete date.
For example:
Person A is born on 1980-02-17.
Person B is born on 1980-02 <- The person doesn't know the exact day (This is acctually a real life scenario)
How to store this wisely in db? You can't really put a datetime DateOfBirth Column.
I usually take 3 int columns (year, month, day) with constraints but i'm wondering...
Does anyone know of a better method?
Legacy Comments
|
Mladen
2007-02-17 |
re: How to store an incomplete date? hmm.... not a bad idea Denis. Not bad at all. :)) Thanx. |
|
ML
2007-02-18 |
re: How to store an incomplete date? How about: if (object_id('dbo.Dates') is not null) begin drop table dbo.Dates end go create table dbo.Dates ( CompleteDate datetime ,IncompleteDate varchar(6) constraint chk_Dates_IncompleteDate check ((1 = case len(IncompleteDate) when 6 then isdate(IncompleteDate + '01') when 4 then isdate(IncompleteDate + '0101') else 0 end) or (IncompleteDate is null)) ,constraint chk_Dates_CompleteDate check ((CompleteDate is not null and IncompleteDate is null) or (CompleteDate is null and IncompleteDate is not null)) ) go insert dbo.Dates ( CompleteDate ,IncompleteDate ) select null as CompleteDate ,'198002' as IncompleteDate union all select null ,'1980' union all select '19800204' ,null go select * from dbo.Dates go All you need now is some additional logic for data adding/modifying/retrieval and you're set. ;) ML |
|
Mladen
2007-02-19 |
re: How to store an incomplete date? Thanx Matija. nice one. I like Denis's more because it's narrower and because both columns have to be always used i can add a Non clustered index on both of them. Or have i missed something in your solution? |
|
ML
2007-02-20 |
re: How to store an incomplete date? On second look, Denis's solution is better. The only thing I'd change, though, is the DateCorrectness column - I'd go with the bitmap: 0 = correct 1 = day unknown 2 = month unknown 3 = 1 + 2 4 = year unknown 5 = 1 + 4 6 = 2 + 4 7 = 1 + 2 + 4 With this modification you can test the value using the logical AND (&) operator. ML |
|
Mladen
2007-02-20 |
re: How to store an incomplete date? i was aiming for a bitmap also. :) |
|
Arne D Halvorsen
2007-04-25 |
re: How to store an incomplete date? This has been pondered in both the GEDCOM and ISO 8601 standards. GEDCOM is for representing genalogical data, with all the uncertainty involved (born either the 30 or 31 of january 1532, by a gregorian calendar, etc) ISO 8601 is an international standard for the textual representation of date/time/intervals. ISO 8601 allows you times like 2007W32: Week 32 in year 2007. It obviously depends what sort of unknowns you need to handle: unknown: large/small set of values for date, month, year same for: week/weekday (some sunday during 2007?) between date x and date y either date x, y, z born during orthodox easter 1634 born during a full moon early 1314 or 1313 etc etc etc. I'd suggest storing an ISO8601 compliant string - there should be libraries to translate back and forth. gedcom: http://homepages.rootsweb.com/~pmcbride/gedcom/55gcch2.htm#DATE_EXACT iso 8601: http://www.cl.cam.ac.uk/~mgk25/iso-time.html |
|
Mladen
2007-04-25 |
re: How to store an incomplete date? thanx for this Arne. very usefull. |
|
raveman
2007-05-05 |
re: How to store an incomplete date? i disagree, can i go register in some real org and not give my home address ? I dont remember it, because i have it written down. What about aliens ? then dont have surname :> are you sure that one day an alien wont be using the system? Person B is born on 1980-02-01 :> I think you are worried about wrong problems, its just like a elevator desinger was worried about how blind and without hands people will use it :> |
|
Mladen
2007-05-05 |
re: How to store an incomplete date? so to which problem are you acctually reffering to, raveman? because we acctually have some people that don't know their exact birthdate. |