Since you posted in an SQL 2012 forum, you could look at LAG & LEAD.

A non 2012 solution to this would be something like this:

;with cte as

(

Select categoryID, effectivedate, categoryvalue,

ROW_NUMBER() over(order by categoryID, effectiveDate) as RowNum

from cattimelines

)

select c.categoryID, c.effectivedate, DATEADD(dd, -1, n.effectivedate) termDate

from cte as c

left outer join cte as n

on c.categoryID = n.categoryID

and c.RowNum = N.RowNum-1