SQL Server - Deleting rows between a date range using SQL. Date conversion fails

Question

DELETE FROM BIZ 
WHERE [Orgnl_Cmpltn_Date]
      BETWEEN '2014-02-31'  AND '2014-04-01'

This is the DELETE statement I wrote. There is an error that says:

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

I know I have to write the correct date format, but I am not sure how that goes.

This question has not been answered elsewhere because the answers I saw did not specify date format (in the context that I am asking for)

Answer 1

You wrote 31st of February... Maybe..... that date doesn't exists.

DELETE FROM BIZ 
WHERE [Orgnl_Cmpltn_Date]
BETWEEN '2014-02-28'  AND '2014-04-01'

For a general idea of convert date:

DELETE FROM BIZ 
WHERE [Orgnl_Cmpltn_Date]
BETWEEN CONVERT(date,'2014.02.28',102) and CONVERT(date,'2014.04.01',102)

Here you can find the complete list of values for third parameter of CONVERT https://msdn.microsoft.com/en-us/library/ms187928.aspx