How to convert varchar to datetime in T-SQL?

You will use a CAST() or CONVERT() on your field:

Declare @dt varchar(20)
set @dt = '08-12-2012 10:15:10'
select convert(datetime, @dt, 101)

For your query you would do the following:

insert into table2
select s.acty_id, s.notes_datetime, s.notes_data
from table1 t 
right join table2 s 
    on t.acty_id =s.acty_id 
    and convert(datetime, t.notes_datetime, 101) = s.notes_datetime
where t.acty_id is null

The right answer is to correct table1 so that it is using the right data types. In the meantime, assuming you need to match both date and time, you can try this:

and CONVERT(DATETIME, t.notes_datetime, 101) = s.notes_datetime