T-SQL converting VARCHAR to date

A customer gave me a database with all the fields in VARCHAR(150). I have a date field in the format dd/mm/yy that I am willing to convert in type DATE. I have found that you can manage to change the type of the date with the function CONVERT like this:

select convert(varchar, getdate(), 3)

which gives:

30/12/06

But it only works if the field is recognized as a DATE.

My request gives is :

SELECT TOP(1000) CAST("Date" AS date) FROM "1_MVENTE" 

And I have this error message :

Conversion failed when converting date and/or time from character string.

It fails when the day is getting above 12 because T-SQL is expectid an American date format. For example 12/12/2021 works but not 13/12/2021.

I don't know how to explain to T-SQL that my input field is a VARCHAR but has to be read as a date dd/mm/yy to be transformed.

Use CONVERT a style code. Also switch to TRY_CONVERT which will return NULL when the value cannot be converted, as it is very likely you have poor data quality and so invalid dates (like '29/02/21', '03/17/22' or '33/12/20'). Style 3 is dd/MM/yy

SELECT TRY_CONVERT(date,[date],3)
FROM dbo.[1_MVENTE];

Of course, what you really should be doing is fixing your design. A column called date that is actually storing a varchar is by definition not a date. varchar is not a "one size fits all" data type; use the correct data type for your data.