2 Expanded answer
source | link

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar).

In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE function.

Example:

SELECT PARSE('21/11/2014' AS datetime USING 'it-IT')

You can read more about date to string and string to date datatype conversion in this article: http://www.sqlservercentral.com/articles/T-SQL/88152/

Back to your issue, add a new datetime column, for instance newCol, update the values parsing the strings, drop the old column and rename the new column to the original name:

ALTER TABLE YourTable ADD newCol datetime NULL;
UPDATE YourTable SET newCol = PARSE(oldCol AS datetime USING 'it-IT');
ALTER TABLE YourTable DROP COLUMN oldCol;
EXEC sp_rename 'YourTable.newCol', 'oldCol', 'COLUMN';

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar).

In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE function.

Example:

SELECT PARSE('21/11/2014' AS datetime USING 'it-IT')

You can read more about date to string and string to date datatype conversion in this article: http://www.sqlservercentral.com/articles/T-SQL/88152/

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar).

In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE function.

Example:

SELECT PARSE('21/11/2014' AS datetime USING 'it-IT')

You can read more about date to string and string to date datatype conversion in this article: http://www.sqlservercentral.com/articles/T-SQL/88152/

Back to your issue, add a new datetime column, for instance newCol, update the values parsing the strings, drop the old column and rename the new column to the original name:

ALTER TABLE YourTable ADD newCol datetime NULL;
UPDATE YourTable SET newCol = PARSE(oldCol AS datetime USING 'it-IT');
ALTER TABLE YourTable DROP COLUMN oldCol;
EXEC sp_rename 'YourTable.newCol', 'oldCol', 'COLUMN';
1
source | link

As you probably have already found out, dates should be stored as dates and not as their human readable representation (varchar).

In order to convert from string to date, you have to use the conversion functions available in your version of SQL Server (which seems to be 2014 if it is the same as Management Studio). In this case, you can use the PARSE function.

Example:

SELECT PARSE('21/11/2014' AS datetime USING 'it-IT')

You can read more about date to string and string to date datatype conversion in this article: http://www.sqlservercentral.com/articles/T-SQL/88152/