I liked your analysis of the different options for building a resultset that included both an in-effect and an out-of-effect date, BUT I think you missed the boat on the final goal of showing the price for each product as of a given date and time. You are essentially looking for the record that has the greatest in-effect date that is less than or equal to the given date and time and I believe the following query will do what you want in a simpler manner:
DECLARE @DateOfInterest DATETIME = '2013-07-31 15:22';
SELECT [p].[ProductID], [p].[ProductDescription], [w].[ProductPrice]
FROM [dbo].[Products] AS p
INNER JOIN (
SELECT [ProductID], [EffectiveStartDT], [ProductPrice]
, ROW_NUMBER() OVER (PARTITION BY [ProductID] ORDER BY [ProductID], [EffectiveStartDT] DESC) AS [RowNo]
WHERE [EffectiveStartDT] <= @DateOfInterest
) AS w
ON [w].[ProductID] = [p].[ProductID]
AND [w].[RowNo] = 1
ORDER BY [w].[ProductID];
I do not believe the out-of-effect date you worked so hard to determine provides any value in finding the correct price. It appears that you have fallen into the trap that many developers fall into (including myself!) of making a query more complex than it needs to be to solve the business need because we think we have found a really cool technique and want to use it. I can't tell you how many times I have gone back to look at code I wrote a year or two ago and wondered why I used complex code to do something that could have been done in a more simple and straightforward way.
Still, it was a very interesting and good article!