Date conversion in SQL from 10-09-2020 to 2020-09-10

I want to convert it for comparing data & here I am stuck.

I have data in date column like 10-09-2020

where convert(varchar, date, 111) >= '2020-09-10'
  AND convert(varchar, date, 111) <= '2020-09-10' 

I tried 43 also at the place of 111

Answer

If you have a date, don’t convert it to a string: compare it as a date instead:

where date >= '20200910'

YYYYMMDD is the safest string format in SQL Server to represent a date.

---

Edit

If your are storing your dates as strings in format DD-MM-YYYY, then you can turn them to dates first, the compare:

where datefromparts(right(date_string, 4), substring(date_string, 4, 2), left(date_string, 2)) >= '20200910'

You should consider changing your data model to use the proper datatype. Storing dates as strings is inefficient and unsafe for the integrity of your data. See the answer by Larnu on how to proceed to convert the datatype of your column.