Couple of basic things ...

Generally you don't want to worry about formatting on the database side. The db should just be storing the date as a date ... the user interface is where the formatting should take place. If you have to format on the db side, what you're going to end up with after using the convert function is a varchar, so it won't work to store that in a datetime variable ... the datetime variable will not maintain your format, it will just be whatever the standard datetime format is on your system. For example, on my system:

declare @date datetime

set @date = CONVERT(varchar(10),getdate(),101)

select @date

returns

--2010-06-09 00:00:00.000

while

declare @date varchar(10)

set @date = CONVERT(varchar(10),getdate(),101)

select @date

returns

--06/09/2010

Again though, you really don't want to store any dates as varchar/char fields ever ... you'll just end up with a mess.

And also, this may seem trivial, but anywhere you're doing an insert into a table, you should specify the columns you're inserting into in the correct order. This will allow you to avoid problems should the column order of the table ever change.