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)

Solution 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

Solution 2

Use this instead

DELETE FROM BIZ
WHERE [Orgnl_Cmpltn_Date] >= '2014-02-28'
AND [Orgnl_Cmpltn_Date] <= '2014'04'01'

I don't know if this matters, but February has only 28 or 29 days.

Solution 3

I assume you use SQL Server, try this..

    DELETE FROM BIZ 
    WHERE CONVERT(DATE,[Orgnl_Cmpltn_Date])
    BETWEEN CONVERT(DATE,'2014-02-28') AND CONVERT(DATE,'2014-04-01')

Solution 4

a couple of things

1) There is no such Date as February 31st, this could be a problem.

2) If you put your date range in the following format, you may have more luck:

BETWEEN '20140228'  AND '20140401'

Let me know how you get on :-)

View more solutions

Comments

[ObserveDBA]

OH WOw! THanks Galma88. You are a genuis. That date does not exist.

[ObserveDBA]

This worked actually. Thank you. For future sake, how do I specify the date format

[Galma88]

Which RDBMS are you using?

[Paul Maxwell]

that is a good date literal format for mssql, but not for other dbms,

[xiawi]

While this code may answer the question, providing additional context regarding why and/or how this code answers the question improves its long-term value.

[hongsy]

please edit your answer to ensure that it improves upon other answers already present in this question.