Your INSERT query doesn't work. I fixed it like this:

insert into #mytable(current_week)

select '2012-02-24 ~~ 2012-03-01' union all

select '2012-01-20 ~~ 2012-01-26' union all

select '2012-01-20 ~~ 2012-01-26 ' union all

select '2012-01-20 ~~ 2012-01-26 ' union all

select '2012-01-27 ~~ 2012-02-02' union all

select '2012-02-03 ~~ 2012-02-09' union all

select '2012-02-17 ~~ 2012-02-23' union all

select '2012-02-17 ~~ 2012-02-23'

Then, taking the simplest approach, this query gets the two dates out as dates:

select

cast(left(current_Week, 10) as date) as 'fromdate'

, cast(right(rtrim(current_Week), 10) as date) as 'todate'

from #mytable

However, the simplest approach may not be the right approach if your data is more variable. In that case you'd want to parse the rows with charindex or patindex functions and use substring to pull out the date fields before casting them to date or datetime