Here's a tweak to the code above to get rid of the error...

still not sure how to get your 2nd requirement to concatenate the 2 dates if they're on the same day but different timestamp without doing it through an sp... or a table function...

CREATE TABLE #tmp_end_date

(ID INT,

tgs_rec_eff_dt DATETIME,

tgs_rec_end_dt DATETIME,

SEQNUM INT )

GO

insert into #tmp_end_date values(12345,'15-Mar-2012',NULL,4)

insert into #tmp_end_date values(12345,'10-Feb-2012',NULL,3)

insert into #tmp_end_date values(12345,'2-Feb-2012',NULL,2)

insert into #tmp_end_date values(12345,'05-Jan-2012',NULL,1)

insert into #tmp_end_date values(33333,'31-Jan-2012',NULL,2)

insert into #tmp_end_date values(33333,'12-Dec-2011',NULL,1)

insert into #tmp_end_date values(1300470,'2012-10-08 12:59:36',NULL,3)

insert into #tmp_end_date values(1300470,'2012-10-08 12:59:03',NULL,2)

insert into #tmp_end_date values(1300470,'1905-07-01',NULL,1);

WITH A AS(

SELECT *,dense_rank()OVER(partition BY ID ORDER BY tgs_rec_eff_dt DESC ) RANK

FROM #tmp_end_date)

SELECT T.ID

,T.tgs_rec_eff_dt

,(SELECT DATEADD(DD,-1,T1.tgs_rec_eff_dt)

FROM A T1

WHERE T1.RANK+1=T.RANK

and T.ID = T1.ID) End_Date

,SEQNUM

FROM A T

ORDER BY 1,2 DESC

drop table #tmp_end_date