March 17, 2012 at 8:53 am
Hi Fellow Members,
I have Human Resource data containing many records with years only in Date of Birth column. Currently I'm storing 01-01-1967 if only 1967 is given as year.
How can year only be stored in date type of SQL Server 2008? (may look like [00-00-1967, which seems not possible because calendar control in C# won't allow 00 either as date or month] or similar)
How much the performance would suffer due to this year storage thing?
Thanks,
Faizan
March 17, 2012 at 11:59 am
For year only I wouldnt use a calendar control.. From the interface perspective I'd use a drop-down.
This is a case where I would likely use a smallint to store the value, two bytes of storage..
CEWII
March 17, 2012 at 1:10 pm
revozex (3/17/2012)
Hi Fellow Members,I have Human Resource data containing many records with years only in Date of Birth column. Currently I'm storing 01-01-1967 if only 1967 is given as year.
How can year only be stored in date type of SQL Server 2008? (may look like [00-00-1967, which seems not possible because calendar control in C# won't allow 00 either as date or month] or similar)
How much the performance would suffer due to this year storage thing?
Thanks,
Faizan
Don't make the mistake of trying to store formatted data. Store it as the 01-01-1967 DATETIME datatype and display it using something else. If you need to, add a parallel flag column which indicates the type of date (year only, year and month only, full date) you're storing so you can display it properly.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 18, 2012 at 11:54 am
revozex (3/17/2012)
Hi Fellow Members,I have Human Resource data containing many records with years only in Date of Birth column. Currently I'm storing 01-01-1967 if only 1967 is given as year.
How can year only be stored in date type of SQL Server 2008? (may look like [00-00-1967, which seems not possible because calendar control in C# won't allow 00 either as date or month] or similar)
How much the performance would suffer due to this year storage thing?
Thanks,
Faizan
First, please clarify the actual data type or types in use; I'm concerned you have a data integrity and/or potential data understanding (with your users) issue.
You said "many" records have only a (valid) year; are there records with valid months and years, but not valid days? Valid days, months, and years, but not valid times? Valid hours, days, months, and years, but not valid minutes, etc. (i.e. is your business something like, say, an Ob/Gyn's office, where times of birth might be important when you have them)?
If _all_ your records contain only a valid year, and will only ever contain a valid year, and never another valid time unit, I would do one of two things:
1) Calculate the largest possible range of years the system will deal with, and then use an integer type with an offset, perhaps TINYINT with an offset (say, 1850 + TINYINT, for a range of 1850 to 2105), or SMALLINT without an offset.
2) Use a DATE type column with a trusted and enabled constraint the guarantees that the month and day will always be 1. Should you fail to put this constraint in, it is near-certain that you _will_ eventually get data in there, somehow, that has a month and day of something other than Jan 1... and then any date arithmetic you do may return invalid results.
If you _do_ have records with varying degrees of precision, you're in real trouble, because ideally you'd want to tell the difference between a New Years, 1990 baby, and a 1990 baby with an unknown month and day of birth. I shudder to think of it, but perhaps independent integer columns with CHECK constraints to cover each time unit (year, month, day, hour, minute, second, millisecond, etc.), and a well-defined value that means "unknown", whether it be NULL or -1 or 0 or 987654321 or ..., would both result in a pretty nasty CASE statement for age calculations... and data that not only is as accurate as possible, but data which will clearly and unambiguously state how precise it is for each individual record.
Jeff, I must respectfully disagree with using DATETIME for date of birth in 2008 at all, unless there will be valid and meaningful times to record, as well as valid dates to record.
March 18, 2012 at 12:56 pm
just my 2ct 😉
So you have "missing" ddmm in some cases. ( or all cases ? )
Are you going to store the actual birth date from now on ?
If yes, store all dates for which you currently only have the year information at 01/01 and indicate you only had year info at conversion/migration time. ( just to be able to know actual from replacement values )
For SQL2008 you can use the date data type.
If you only want to present year info at select time, you could translate year to a "between 01-01 and 31-12" for your queries.
Maybe even use a persisted calculated column to store / extract year info. But I would prefer storing date info in the date data type whenever possible. The need for date processing may not be actually wanted at first, but you'll find that desire pretty soon in a projects life cycle.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 22, 2012 at 7:46 pm
Nadrek (3/18/2012)
Jeff, I must respectfully disagree with using DATETIME for date of birth in 2008 at all, unless there will be valid and meaningful times to record, as well as valid dates to record.
Yep. You're right. Unless the time of birth is available in the information to be strored, you could store the DOB as DATE. Since the data the OP is dealing with is pretty much a mess and a birth date could include a time, I suppose it's OK to throw that information away (just not on my watch). 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
March 22, 2012 at 8:40 pm
@All: Thankyou so much for your detailed replies.
The organization has many employees born before 50/60 and don't have their Exact date of birth written on their National Identification Cards ( i.e. They don't have day and month component of the DATE)
This is not true with all the employees as 70% of staff have have their actual birthdays with valid day, month and year components.
@UI Constraint: Client only wants to see the data of birth as Calendar Control while setting up or editing any employee.
So we employees having valid date and employees not having valid date should be stored in same Date of Birth Column of database.
Thanks,
Faizan
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply