Storing partial birth date
-
We need to store both Full and Partial Dates (whichever we have). Currently we are using two fields to do this - a datetime field to store full DOBs and a char(4) field to store the year. Is there any better way to do this ?
Thanks
-
I can't imagine a situation where it is usefull to store incomplete (wrong) data. Do you have cases where the person doesn't know its DOB, or forgot the year??
The only thing that comes to mind at the moment would be to have one date column and a flag column that would advise that the information may be wrong... Other than that I'll need your feedback to provide better solutions.
-
Remi - this may not be "wrong" data necessarily - it could be DOB or year of birth!
Since you already have a date field you could use it to store the YOB as well and default it to (eg: '01/01/YYYY') and have the flag column indicate that it is a YOB column and not the actual date of birth...one way of doing it!
**ASCII stupid question, get a stupid ANSI !!!** -
Just to back up sushila, I was helping a friend import some genealogy data. She had a lot of data where she knew the year of birth and sometimes the month and year, but not the actual date.
I suggested she take a similar course of action described above, although in her case the flag indicated an incomplete date.
--------------------
Colt 45 - the original point and click interface
-
Thanks all for your suggestions.
Yes, we do need to store partial dates such as YOB if we have only that and not the full DOB, as we have match points set on it and search on partial dates as well.
Sushila, I do like your suggestion, and might look into implementing it, but I was trying to look for a solution where we could store it all in just 1 column, but seems like it wont be possible in this scenario.
If there are any other ways to do this, please let me know. Thanks again, for your inputs.
-
I would definitely not store it in a single column. DOB and YOB does not represent the same information and are not even the same datatype. If there are requirements to sometimes use them together (for instance a search for persons older than one person but younger than another) then it is an issue for the query to decide how to treat them together.
-
Can the SQL_variant data type be used to store bothe DOB and Partial DOB in the same column ?
-
I would suggest still using a datetime column, but add another column that holds a code we call it DatePrecision. The code tells us how much of the datetime data was actually provided by the customer and how much is defaulted.
For example: Our DatePrecision codes look like this
'y' = Only year was provided
'm' = Month and year provided
'd' = Month year and day provided
etc...
This way if we get a datetime of '01-Jan-1981 00:00:00:000'
with a DatePrecision code of 'y' we know that the 01 jan was just the default and the customer only ascerted the year.
Granted, there are problems with this method, but I don't like the idea of storing dates in varchar fields. This way, we can ensure that the data is at least a valid date...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Thank you, But I don't think there is a way to know what is the default and what was provided. For eg, we have several people with full and valid DOBs such 01/01/1975 in our database and we ONLY receive either the full DOB or just the year. So we can't use defaults of 01/10/yyyy.
However, can the sql_variant datatype be used in this scenario. We can continue to treat full DOBs as datetime as YOB as char ?
-
Again, IT'S NOT THE SAME DATA, split it in two columns as basic design rules tell us to do.
-
It isn't as simple as that Remi. Let's say you split the data out into Year, Month, and Day columns, we already know that in some cases one or more of those columns won't be provided so now we have to deal with NULLs.
Either way is a logical problem, but by splitting the data out into seperate columns, now you have created a situation were even the basic enforcement of the fundamental data type (does this data represent a valid date?) becomes difficult. In some cases, splitting date information into its more atomic parts makes perfect sense, but this doesn't appear to be one of those cases.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
If it's ONLY the full DOB OR just the year, what's the argument against storing the YEAR ONLY value as a '01/01/yyyy' and using the flag indicator to specify that it is year only.....????
**ASCII stupid question, get a stupid ANSI !!!** -
To add to DC's proposition. It's what I would do in this case :
DOB datetime (or smalldatetime depending on the range). Then add a precision colum char(3) in the format YNY where each byte represent complete/incomplete information. This keeps all the information in 1 table, in their correct datatype and in only 2 columns without allowing nulls. It also provide any possible combinaison of missing information. Note that a tinyint column could also do the job for the precision column using bitwise operations.
-
okay - I'm going to ask for one LAST time and then forever hold my peace....
if the data is ALWAYS going to be full DOB or Year (as poster indicated)...what are the arguments AGAINST storing the year only data in the same field as some default (you pick the default if you don't like '01/01/yyyy') and having a flag column indicate that the datetime is "year only" or "full DOB" ?!?!?!?!?!?!?!?!?!?!?!?!?!?!?!
when the flag indicates "year only" you get a datepart(yyyy, date) & otherwise you get the value as is....?!?!?!?!?!?!?!?!?!?!?!?!?!?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
He said : "We need to store both Full and Partial Dates (whichever we have)." He talks about storing the year but he doesn't say if it's enough and why he's wondering if it's the best design.
My version allows to have any incomplete information possible where yours only speaks about year but I don't know if it's a requirement.
So I would say Peace
.
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply