SCD type 2 - Valid To Date Value? 12/31/9999 or Null?

View previous topic View next topic Go down

SCD type 2 - Valid To Date Value? 12/31/9999 or Null?

Post  kinu008 on Wed Apr 28, 2010 12:09 pm

Hello all,
While populating Effective End Date (Valid to date) for current record, is it better to put a largest possible end date value of 12/31/9999, or is it better to insert a null value. One advantage of inserting 12/31/9999 as end date is that oracle 'between' function can be used effectively while doing a search on date range.

I just want to find out if there are any drawbacks of inserting 12/31/9999 as end date value. I would also like to know if there are any advantages of populating null value? I did search on the forum for an answer but I couldn't find one.

Appreciate your response.

kinu008

Posts : 2
Join date : 2010-04-28

View user profile

Back to top Go down

Re: SCD type 2 - Valid To Date Value? 12/31/9999 or Null?

Post  ngalemmo on Wed Apr 28, 2010 12:19 pm

Common practice is to use some known future date as the expiration date for a current record. So, if your database accepts 12/31/9999, then use that (or 12/31/2999 if 9999 is too large). Always use the same date throughout to make it easy to find the current row.

Do not use a null. It only complicates things when you try to find a row using a BETWEEN expression. Also, databases usually do not include null values in their indexes, making it more difficult (slower) to locate them.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: SCD type 2 - Valid To Date Value? 12/31/9999 or Null?

Post  kinu008 on Wed Apr 28, 2010 12:39 pm

Thanks much..

kinu008

Posts : 2
Join date : 2010-04-28

View user profile

Back to top Go down

Re: SCD type 2 - Valid To Date Value? 12/31/9999 or Null?

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum