Best practice for SCD2 start and end dates

What is the best practice for setting the end date of the prior row?

1. Same as effective date for current row?
2. Day prior to the effective date for current now?
3. Day prior at 11:59.999?
4. Something else?

I've seen 1 & 2 in Kimball examples before, not sure which is the accepted best practice these days.

Thanks,
Nemo

Generally the end timestamp should be set to some time prior to the new start timestamp. Such as subtracting a millisecond from the new start. This allows use of BETWEEN, which is an inclusive range, when filtering.

If you are using a DATE type which does not carry time of day (this varies by database) then the end date should be the day prior to the new start date.

I've worked places that use both of those solutions. Most of the companies set effective start equal to the previous row's end date or timestamp if you do multiple loads during the day. Either way works.

Thanks guys, I've seen it done all different ways, wasn't sure what consensus was on "best". Thanks!

I think there is a 'best', and it is the solution proposed by ngalemmo.

The use of BETWEEN should not be disregarded. Even if it is not part of your original plans, someone, somewhere, will use it in future.

Ron.

I agree.
If you set end_time = start_time on "sequential" Dim records then you have to introduce logic to use either ">= Start_time" or "<= end_time" when determining the applicable Dim record.
If your set end_date < start_date so that the Dim records don't overlap there can never be any confusion