SQL Server - 使用 SQL 删除日期范围之间的行。日期转换失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33119736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server - Deleting rows between a date range using SQL. Date conversion fails
提问by ObserveDBA
DELETE FROM BIZ
WHERE [Orgnl_Cmpltn_Date]
BETWEEN '2014-02-31' AND '2014-04-01'
This is the DELETEstatement I wrote. There is an error that says:
这是DELETE我写的声明。有一个错误说：
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)
这个问题没有在其他地方得到回答，因为我看到的答案没有指定日期格式（在我要求的上下文中）
回答by Galma88
You wrote 31st of February... Maybe..... that date doesn't exists.
你写的是 2 月 31 日……也许……那个日期不存在。
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 CONVERThttps://msdn.microsoft.com/en-us/library/ms187928.aspx
在这里您可以找到https://msdn.microsoft.com/en-us/library/ms187928.aspx 的第三个参数的完整值列表CONVERT
回答by japzdivino
I assume you use SQL Server, try this..
我假设你使用 SQL Server，试试这个..
DELETE FROM BIZ
WHERE CONVERT(DATE,[Orgnl_Cmpltn_Date])
BETWEEN CONVERT(DATE,'2014-02-28') AND CONVERT(DATE,'2014-04-01')
回答by Hussain Alaidarous
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.
我不知道这是否重要，但二月只有 28 或 29 天。
回答by Stevie Gray
a couple of things
几件事
1) There is no such Date as February 31st, this could be a problem.
1) 没有 2 月 31 日这样的日期，这可能是一个问题。
2) If you put your date range in the following format, you may have more luck:
2）如果您将日期范围设为以下格式，您可能会更幸运：
BETWEEN '20140228' AND '20140401'
Let me know how you get on :-)
让我知道你是怎么办的 ：-）
回答by Ajit
You can use the following code:
您可以使用以下代码：
DELETE from Tabel_Name WHERE Date BETWEEN CONVERT(datetime,'01/01/2001',103) and CONVERT(datetime,'31/12/2001',103)
