Storing partial birth date
-
Isn't that essentially what I said? I just clarified that my preference would be that the column be a datetime data type rather than varchar or anything else. If you supply just the year to SQL Server it will automatically default the month to Jan and the day to 01 and the time to midnight. The flag just tells you how much of that data was supplied versus how much was defaulted (and thus is unreliable).
On reading through the entire post again, I can see now that that is essentially what you were suggesting, so you and I are on the same page from what I can see...
/*****************
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
*****************/
-
Yes but the flag system is somewhat different, you say Y is for year, M is for year and month, d is for all. Where I say YNY as in yes for year, no for month and yes for day. This allows for all possible combinaison of missing/present information and doesn't assume that if you know the day you were born, that you know the year and month too.
-
Phew!!!! For some time I thought my responses were visible only to me and no one else...okay - so long as we're all talking the same language I'm fine...I only wanted to know why all the splitting business was required is all (which's what I thought everyone was advocating)....
Peace to you too Remi!
**ASCII stupid question, get a stupid ANSI !!!** -
But if it's only one or the other it can always be just Y for year and whatever else you want for full DOB (take your pick - "D", "N"......)
**ASCII stupid question, get a stupid ANSI !!!** -
Thank you all for your useful suggestion
Currently we have only 3 possibilities, but may change in the future :
1) Full DOB (mm/dd/yyyy)
2) Only year (yyyy)
3) No DOB / NULLS
So, I am sure any or all of your suggestions can be incorporated
Thanks again !
-
It's a variation on the same basic theme. In our case we required at least the year, and if the day was specified we required the month. If the requirements of your system are different use whatever variation supports the requirements.
/*****************
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
*****************/
-
Sushila I'm not sure you understand the flag system I propose. Here are all the variants :
Date - CharFlag - TinyintFlag
Year only
1981/01/01 - YNN - 1
Month only
1900/03/01 - NYN - 2
Day only
1900/01/23 - NNY - 4
Year and day
1981/01/23 - YNY - 5
Year and month
1981/03/01 - YYN - 3
Month and day
1900/23/03 - NYY - 6
Complete information
1981/03/23 - YYY - 7
whereas the char flag can be replaced with a tinyint column and check like this for validity :
1 = year
2 = month
4 = day
then just use like this :
where flag & 1 = 1 (year)
where flag & 2 = 2 (month)
where flag & 4 = 4 (day)
...
where flag & 7 = 7 (Full)
-
Yup and while I was typing, the poster gave us his needs so this is a moo point now. Glad we think the same
. -
Now you know what you have to do to solve the current problem... and all the future ones that can come your way
. -
Remi, I think your suggestions and flag system takes care of all possibilities and we will certainly look at implementing something along those lines.
But I was just curious about something else. Have you ever used the sql_variant datatype and in what kind of scenarios is it typically used ?
Thanks
-
Aruram (btw - my last name is left(4, yourname) <;-)
What a wonderfully diplomatic response!
Remi - Yes, I did understand your flag system...you type too fast - "moo(?)" point - kinda cute that!!
**ASCII stupid question, get a stupid ANSI !!!** -
No because it's the wrong design. Sql_variant causes extra overhead to cast to the current type when used (internally) and also needs to have extra metadata maintained in the system tables to keep the current type of the column/variables.
Also [small]datetime will ensure that the data is a valid date at all times and won't allow for corruption like a varchar would do. One last thing is that date columns are great for range search and have a bunch of functions that you'd have to code yourself to use (unless you recast to datetime which is useless overhead).
-
Because I think everyone is entitled to my opinion...
The sql_variant data type is an abomination! I have never used it and I hope I never will be forced to. A data type that is, by definition not a data type is just stupid. How can you possibly hope to model and preserve the meaning of data if you can't even be bothered to determine it's domain. I suppose the sql_variant holds some appeal to those sadly ignorant folks who think a database is nothing more than a way to "persist data."
/*****************
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
*****************/
-
On the same line, Sql_variant is just not data, so why keep it in the database?? If you need to store complexe data that can't be preserved in [n][var]char or in any type of number, then you have the binary datatype which can store anything (word doc, excel worksheet...). But then again this is something I never had to use... but it could happen eventually.
-
Thanks Remi. That makes sense.
Viewing 15 posts - 16 through 30 (of 33 total)
You must be logged in to reply to this topic. Login to reply