Design Question--partial Date data

Hi everyone,
I am working on an SQL Server 2K application that includes a requirement for
~20 "partial" Date fields (spread through several of the database tables).
By partial Date, I am referring to a Date where the Day portion is not
required, the Month portion or may or may not be required (depending on the
particular field), but the Year is always required. When either Month or Day
is not required, they _may_ be entered if known.
Clear so far? OK. I have considered creating a user-defined datatype
'pDate (int)' wherein the date data would be encoded e.g. 20070904 (today) or
20060000 (Year is 2006, Month and Day are unknown) or 19840500 (May 1984).
My thinking behind this approach is that 1.) ints are relatively compact, and
2.) Sort/compare should be easy, particularly since my client wants to use
the 'filing' rule ("Nothing always comes before something") so 19840430 <
19840500 < 19840501 ("May 1984" occurs just after "April 30, 1984" and just
before "May 1, 1984").
Then, I thought of applying a Rule to allow me to validate the data entry
(yyyy0230 is always invalid, but yyyy0229 is OK only in a leap year, etc.).
The beauty of this idea, at least on intial consideration, is that a
user-defined Rule can be associated with the user-defined pDate custom
datatype, and so I wouldn't have to associate (and, perhaps more importantly,
maintain) the same exact check constraint with each of the 20 instances of my
partial-Date fields.
By now you're probably already thinking "Rules are too simple--that won't
work." or "Rules are being deprecated by Microsoft--good luck when your
client upgrades to SQL Server 2008." If so, I agree...I think...but then I'm
a relative newbie, and so I'm not very certain of that.
Question 1: Do you know of a better way to create such a partial-Date data
type?
Question 2: If my 'pDate(int)' idea is reasonable, is there a way to define
a reusable (i.e. single-place definition) check constraint that can be
applied to each of these fields?
Furthermore, recall that some of these "pDate" fields will require a Month,
some will not. I am considering creating and then setting an extended
property on each such pDate(int) field as a way to identify this requirement
because, it seems to me, this might be a good way for my data entry
validation procedure to retrieve "on the fly" and act on this requirement as
desired.
Question 3: Is there a reason why such an extended property won't work?
Question 4: Can you think of a better way to implement such a
field-instance-dependent data validation requirement?
Based on past experience, I am probably missing something blatantly obvious
to an even slightly expert SQL Server developer, so any advice or suggestions
would be most welcome. Thanks in advance.
Best regards,
EdWhy not store Y (smallint), M (tinyint) and D (tinyint) in separate columns.
When M or D is NULL you can use COALESCE to still generate a valid date when
converting. And a separate column (or existing criteria) could be used to
dictate whether M/D can/should be NULL.
A|||Aaron,
Thanks for the suggestion; I think that's a great idea, not to mention much
simpler than where I was headed, and at no additional storage space cost!
Thank you.
Ed
"Aaron Bertrand [SQL Server MVP]" wrote:
> Why not store Y (smallint), M (tinyint) and D (tinyint) in separate columns.
> When M or D is NULL you can use COALESCE to still generate a valid date when
> converting. And a separate column (or existing criteria) could be used to
> dictate whether M/D can/should be NULL.
> A