[AccessD] Re: Using Dates

Hi Robert

Again, I wonder which system, say accounting system, should generate a
date of year 1003. A user might, of course, type something like that,
but the application should have validation rules to determine which
date range is allowed to input. If not, any date can be input by
mistake, and you will have no chance later to determine what it
"should have been".
As for your data warehouse and such dates, a join between the date
field and your date table will rule out such entries as - for a
business application - no join will exist - which to me would be the
proper way to deal with it.

/gustav


> Hi Gustav,

> The best that I can think of is something like processing and ETL 
> (extract,transform,load) routine you run into a date 2/1/1003 that
> was technically correct, but invalid because it should have been
> 2/1/2003.

> Robert

> At 10:42 PM 2/3/2004 -0600, you wrote:
>>Date: Tue, 3 Feb 2004 20:42:30 +0100
>>From: Gustav Brock <gustav at cactus.dk>
>>Subject: Re: [AccessD] Re: Using Dates
>>To: Access Developers discussion and problem solving
>>         <accessd at databaseadvisors.com>
>>Message-ID: <542496076.20040203204230 at cactus.dk>
>>Content-Type: text/plain; charset=us-ascii
>>
>>Hi Robert
>>
>> > What I think he is saying is that in 99% of the cases, the regular date
>> > dimension table will work.  In a few cases, where the date may be missing
>> > and you want to get some kind of value for it, you cannot use the date
>> > itself as the key, thus the surrogate.  The surrogate would be stored in
>> > the fact tables (another data warehousing concept, for those that do not
>> > know about them).  That way when you hit an invalid or null date value, the
>> > date dimension table would be able to handle it in a predefined way.
>>
>>I noticed this too and wondered. How can one "hit an invalid date"? Do
>>dataware house people not validate data before storing them? A missing
>>date may, of course, be accepted, but an invalid? Where would that
>>come from?