How to convert varchar to datetime in T-SQL?

Question

I am trying to populate the data from table1 to table2, both have the same number of columns.

All the columns in table1 are of type varchar. The columns in table2 could be varchar, int or datetime, etc.

My question is how to do the conversion during the populating?

This is a sample query that I wrote. I miss the part to do the conversion. Also the format of my datetime is mm/dd/yyyy hh:mm:ss.

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 t.notes_datetime =s.notes_datetime)
    where t.acty_id is null

Answer 1

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

Answer 2

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