Type-2 Dates as Date Data Type ?

When we have the type-2 ETL Housekeeping columns like Record Effective From Date and Record Effective To Dates as DATE datatypes
and the record is changing next day what would we use to close the existing record.

1. If we use (Row_Eff_Dt of the new record - 1 day) to close the existing record then the Row_Eff_Dt and Row_End_Dt will be same ?
Is that standard practice ?
2. If we use Row_Eff_Dt of the new record to close the existing one then we will get two records when between operator is used.

Is there any other way ?

Code:


On 1/1/2011:

Prod_Dim_Key   Prod_ID(Natural Key)   Prod_Name   Prod_Color   Prod_Group   Row_Eff_DT   Row_End_Dt


P1      100         abc      Blue      xyz      1/1/2011   1/2/2011

On 1/2/2011 :

P2      100         abc      Blue      xyz1      1/2/2011   12/31/9999


Instead of using between, use this where clause: TrasactionDate >= Row_Eff_Dt and TransactionDate < Row_End_Dt.

Or alternatively as in some MS server sample DW, you may expire the SCD 2 record one day earlier to be able to use between without producing duplicate. Both options will work as the logic is just used in ETL process, and the dimension users should not have to worry about it, although I prefer the same date approach as it does not give you impression that there is one day gap.

Hang,
dont you think that end date of previous record and start date of new record should have difference of 1 day otherwise logically seeing records it appearas 2 records were active on a given day.

I have seen both these approaches i.e having 1 day difference and having no diference but I always preferred former one.

Vishy,
If you realise 1/2/2011 really indicates 1/2/2011 00:00:00, you would know what I meant by 'one day gap'. So you may have to think of SCD end date as 1/2/2011 23:59:59 and start date 2/2/2011 as 2/2/2011 00:00:00 to close the gap. However, the same day approach is very close to that when you use datetime data type, ie. 2/2/2011=1/2/2011 23:59:59 (previous end)=2/2/2011 00:00:00(next start).