Comma seperated date splitting without using loop.

--Comma seperated date splitting without using loop.

--// Table variable declarion
Declare @TableVar Table
(
    ID            int,
    CustomDates varchar(max)
)

--// Data insertion
Insert into @TableVar values(1,'May 01 2012  4:00AM,May 05 2012  4:00AM,Apr 13 2012 10:00AM')

select * from @TableVar

;with cte
(
    ID,CustomDates,strDt,pos
)
As
(
    select ID,
        CustomDates,
        substring(
                    CustomDates,1,case when (charindex(',',CustomDates) -1) = -1 then Len(CustomDates) else charindex(',',CustomDates) -1 end
                 ) strDt,charindex(',',CustomDates) as pos
       
    from @TableVar
    union all
    select s.ID,
        s.CustomDates,
       
             substring(s.CustomDates,cte.pos+1,
                    case when (charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1) = -1 then len(s.CustomDates) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates)))-1 end)       

           ,pos + case when charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) = 0 then len(substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) else charindex(',',substring(s.CustomDates,cte.pos+1,len(s.CustomDates))) end as pos


    from @TableVar s inner join cte on cte.ID = s.ID
    where cte.pos < len(s.CustomDates)
)

select strDt from cte OPTION (MAXRECURSION 32767);

---------------------------------------------------------------

Another Method:

Declare @TableVar Table

(

ID int,

Data varchar(max)

)

Insert into @TableVar values(1,'May 01 2012 4:00AM,May 05 2012 4:00AM,Apr 13 2012 10:00AM')


DECLARE @XML XML

SELECT @XML = '<Root><Child><Data>'+REPLACE(Data,',','</Data></Child><Child><Data>')+'</Data></Child></Root>' FROM @TableVar

SELECT CAST(DOC.COL.value('Data[1]','NVARCHAR(400)') AS DATE) AS Data

FROM @XML.nodes('/Root/Child') DOC(COL)