Have you actually tested this? This would work, regardless of if your Dates field in the table Converted_agreement is a date or varchar(255) data type (don't use varchar(255) in your new table 😉 ).
For example, taking your small dataset:
CREATE TABLE #Agreement (Dates varchar(255))
INSERT INTO #Agreement
CREATE TABLE #Converted_Agreement (Dates date, DatesOld varchar(255));
INSERT INTO #Converted_Agreement
SELECT Dates, CAST(Dates as date)
DROP TABLE #Converted_Agreement;
DROP TABLE #Agreement;
This returns the values:
So even though DatesOld is a varchar type, it still displays in yyyy-MM-dd (as this is the display format for date data types) as it was initially cast as as date. As i said though, store your dates as a date, not a varchar. 🙂
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.