August 7, 2007 at 10:00 am
if you had the option beforehand, and you know going in people are only going to give you (on occasion) partial birthdays, would you or would you not break up the birthday into three seperate fields just to be able to take what they have?
I am inclined to leave it as a date field for reporting purposes and leave off anyone who would not give me a full and correct date.
Ideas on development pros and cons here????
August 7, 2007 at 10:04 am
speaking form the standpoint of a bit of experience, too many times I've encountered a situation where dates or partial dates were entered into say, a varchar field, It caused more work later, either converting them, trying to re-interpret dates, date comparisons, etc, that I usually ended up cleaning up the data and converting them back to datetime fields (that they should have been in the first place)
I would suggest forcing datetime fields and also add a constraint to the field to assure valid dates (ie CHECK NEWDATEFIELD > '01/01/1970') so that a value of zero cant get inserted, which is like 01/01/1901, which ends up being another layer of garbage data to filter out.
Lowell
August 7, 2007 at 1:20 pm
I think you have to start with the business requirement and ask:
“Is partial information of any value? Does year alone have any value? Does year and month have any value? Does month alone have any value? Does day of month alone have any value?”
If you decide that you need to capture them, you can create columns to hold the partial information columns: BirthYear, BirthMonth, BirthDayOfMonth. In addition, I would also create a nullable BirthDay column to hold the actual birthday as a datetime if you do get the entire date. I would also add constraints to make sure the individual columns match the full date and to verify the individual columns contain data that can be converted to valid birthdays.
August 7, 2007 at 3:16 pm
A couple of things:
1. Varchar will sort as dictionary order, not as date or integer. I have one varchar field in one third party app for the year of birth and it sorts as: 1999, 2000, 22 etc - 22 was probably somebody who entered how old he is, not year of birth.
2. Data import from Excel may give you extra 0 in front of the number if you are importing as Varchar or will not import at all
3. You can always parse the date of birth later if you enter it as DateTime using DateTime functions, so somebody who did not provide full information will still be counted when creating demographic reports when you are interested in a year, for example.
Regards,Yelena Varsha
August 8, 2007 at 9:19 am
If the birthday is being recorded to support birthday event planning, where the year (age) may be "sensitive" to some, then you can use a datetime field, setting the year to 1900 (or some other flag value) if it was not given.
You can create a UDF that puts the (current or next) year in a the date value so you can select for cake ordering purposes.
If you need the actual age, but people are reluctant to give it, you might consider having them select from a demographic bucket - 20-30, 30-40 for example - if that meets your needs (and store the result as a datetime).
If you are using a value as a "date" you will have headaches if you store it asw anything but a datetime.
August 8, 2007 at 3:57 pm
We should always ensure that the values we hold are correct - resist any suggestion that you enter an incorrect value as a place holder as you then have to increase the complexity of the query in order to ignore the inaccurate values you have populated and/or cannot use a valid data point
E.g. if the column is a birthdate enter a valid birthdate or a NULL. If you chose some other date say 1 Jan 1900 as a flag that means 'we dont know the birth date' you then eitehr cannot accept a person of approx 107 years age or you need another colujmn that indicates if the value of the first is real or crap.
Trust me if your app is of any value it will exist int he business after you and everyone else who knows it has left the business. How will your successors succesor know the magic value you have chosen to be equivilent to NULL? When someone later writes a report on this data via Crystal, SSRS or Excel how will you ensure that the special peice of code you enter into your app to ignore this magic number ins correctly replcated in the other data access application?
In relation theory the only acceptable value for an unkown or unentered value is NULL.
back to your orignal question;
If you can be certain that an atomic item of data is correct and is of value to the business, collect it accurately and use it correctly. Think about the validation (being a date it'll be somewhat difficult) I'd suggest that you require at least 2 sets of validation, a field validation for datatype and range e.g. month must be an int between 1 and 12 and a validation that the three fields combined (day, month, year) make a valid date. It gets harder with a part date, e.g. if I enter 31 in the day is this valid? By itself it is buyt obvious 31/2 is not valid as the 2 month only has 28 or 29 days.
On the other hand if you only need to know the month and year life will be a little easier.
Regards
Karl
August 10, 2007 at 7:50 am
It sounds as thought you don't have a firm requirement on the accuracy of the data, so to your point. I would still store it as a date, and record whatever they tell me. If they don't give a year estimate. Before I would break it up into parts, I would add a flag to say that the birthday is an estimate only.
August 10, 2007 at 3:00 pm
In my apps I store inputted dates as char(8) with any unsupplied parts filled with zeros. My rationale for this is that my apps always record dates in the form of a set of drop-downs, e.g. dd, mm, yyyy or mm, yyyy. I also find it easier to not to have to convert 1/1/1900 to zeros when a date has not been entered. My apps do however have functions that render dates and accommodate zeros.
For all temporal events that are recorded by the system I use datetimes.
August 10, 2007 at 4:42 pm
This is one of those places where properly structured (and really quite rigid) data definition meets the messy, messy, messy real world. So, who wins? Properly structured (but rarely if at all entered and ergo essentially meaningless) data or messy, not convertible, full of rot, whatever the user typed, real world junk? 😉
Since we're talking about birthdays here, I'd recommend one of two things:
1)If this is filling a primarily social function (celebrating birthdays), then have one field for the day and one for the month - with the appropriate constraints/triggers to ensure they are valid - and skip the year entirely.
2)If we're talking about a necessary data field (say for medical care eligibility) then use a proper date field and make it "NOT NULL".
Steve G.
August 13, 2007 at 11:28 am
1. One ALWAYS has the option beforehand.
2. What's the business use of capturing this fractionated date?
3. How will the content be retrieved?
Answer those two questions and I can give you a real-world based response. Note that the method of entering the data(date) is independent of the answer you receive. There are many ways to skin a cat. But skinning an alligator is a totally different task.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply