The first impression is that you create the 22th because you first insert 23rd and then you substract 1 day. Try commenting out the
PDATE @temp SET
Date_From = CASE WHEN [Version] = 1 AND @Beg BETWEEN Date_From AND Date_To THEN DateAdd(dd, -1, @Beg)
WHEN [Version] = 2 AND @Beg BETWEEN Date_From AND Date_To THEN DateAdd(dd, +1, @Beg)
ELSE Date_From END,
Date_To = CASE WHEN [Version] = 1 AND @Beg BETWEEN Date_From AND Date_To THEN DateAdd(dd, -1, @Beg)
WHEN [Version] = 2 AND @Beg BETWEEN Date_From AND Date_To THEN DateAdd(dd, +1, @Beg)
ELSE Date_From END
portion to see this.
[Addition]
Based on the desription, could you try something like this:
DECLARE @Product TABLE ( Date_From Datetime, Date_To Datetime, Product_ID INT)
INSERT INTO @Product Values ('20110323', '20110324', 101);
INSERT INTO @Product Values ('20110325', '20110326', 101);
INSERT INTO @Product Values ('20110327', '20110329', 101);
DECLARE @Beg Datetime, @End Datetime
SET @Beg = '20110323'
SET @End = '20110323'
UPDATE @Product SET
Date_From = CASE
WHEN Date_From BETWEEN @Beg AND @End THEN NULL
ELSE Date_From
END,
Date_To = CASE
WHEN Date_To BETWEEN @Beg AND @End THEN NULL
ELSE Date_To
END;
DELETE FROM @Product
WHERE Date_From IS NULL
AND Date_To IS NULL;
UPDATE @Product
SET Date_From = CASE
WHEN Date_From IS NULL THEN DATEADD(day, +1, @End)
ELSE Date_From
END,
Date_To = CASE
WHEN Date_To IS NULL THEN DATEADD(day, -1, @Beg)
ELSE Date_To
END;
SELECT * FROM @Product
ORDER BY Date_From