将 varchar 数据类型转换为 datetime 数据类型导致 SQL 查询中的值超出范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28271414/
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
Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
提问by Noora
I have a table with a column that stores the date and time. I need to write a query to get only the date from that column,
我有一个表，其中有一列存储日期和时间。我需要编写一个查询以仅获取该列中的日期，
SELECT CAST(CONVERT(VARCHAR, LoginTime, 101) AS datetime) FROM AuditTrail
But, when I run the query I am getting this error:
但是，当我运行查询时，我收到此错误：
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。
the data in the column is datetime ex: 2012-06-18 12:08:04.000 so i need to extract the date only and remove the time note that the [Logintime] column is datatime format
该列中的数据是 datetime ex: 2012-06-18 12:08:04.000 所以我只需要提取日期并删除时间注意 [Logintime] 列是数据时间格式
回答by Sarath Avanavu
Try ISDATE()function in SQL Server. If 1, select valid date. If 0 selects invalid dates.
ISDATE()在 SQL Server 中尝试函数。如果为 1，则选择有效日期。如果 0 选择无效日期。
SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)
FROM AuditTrail
WHERE ISDATE(LoginTime) = 1
- Click hereto view result
- 单击此处查看结果
EDIT :
编辑 ：
As per your update i need to extract the date only and remove the time, then you could simply use the inner CONVERT
根据您的更新，我只需要提取日期并删除时间，然后您就可以简单地使用内部CONVERT
SELECT CONVERT(VARCHAR, LoginTime, 101) FROM AuditTrail
or
或者
SELECT LEFT(LoginTime,10) FROM AuditTrail
EDIT 2 :
编辑 2：
The major reason for the error will be in your date in WHERE clause.ie,
错误的主要原因在于您在 WHERE 子句中的日期。ie，
SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <=
CAST('06/18/2012' AS DATE)
will be different from
将不同于
SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <=
CAST('18/06/2012' AS DATE)
CONCLUSION
结论
In EDIT 2the first query tries to filter in mm/dd/yyyyformat, while the second query tries to filter in dd/mm/yyyyformat. Either of them will fail and throws error
在EDIT 2 中，第一个查询尝试按mm/dd/yyyy格式过滤，而第二个查询尝试按dd/mm/yyyy格式过滤。它们中的任何一个都会失败并抛出错误
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。
So please make sure to filter date either with mm/dd/yyyyor with dd/mm/yyyyformat, whichever works in your db.
因此，请确保使用mm/dd/yyyy或使用dd/mm/yyyy格式过滤日期，以您的数据库中适用的为准。
回答by Vahid Farahmandian
hope this may help you:
希望这可以帮助您：
SELECT CAST(LoginTime AS DATE)
FROM AuditTrail
If you want to have some filters over this datetime or it's different parts, you can use built-in functions such as Year and Month
如果你想在这个日期时间或者它的不同部分有一些过滤器，你可以使用内置函数，比如 Year 和 Month
回答by Marcello Miorelli
as you can see on the answer to this question: Conversion of a varchar data type to a datetime data type resulted in an out-of-range value
正如您在此问题的答案中所见： 将 varchar 数据类型转换为日期时间数据类型导致值超出范围
-- set the dateformat for the current session
set dateformat dmy
-- The conversion of a varchar data type
-- to a datetime data type resulted in an out-of-range value.
select cast('2017-08-13 16:31:31' as datetime)
-- get the current session date_format
select date_format
from sys.dm_exec_sessions
where session_id = @@spid
-- set the dateformat for the current session
set dateformat ymd
-- this should work
select cast('2017-08-13 16:31:31' as datetime)
回答by Abdelhakim Ezzahraoui
some problem, but I find the solution, this is :
一些问题，但我找到了解决方案，这是：
2 February Feb 28 (29 in leap years)
2 月 2 日 2 月 28 日（闰年 29）
this is my code
这是我的代码
public string GetCountArchiveByMonth(int iii)
{
// iii: is number of months, use any number other than (**2**)
con.Open();
SqlCommand cmd10 = con.CreateCommand();
cmd10.CommandType = CommandType.Text;
cmd10.CommandText = "select count(id_post) from posts where dateadded between CONVERT(VARCHAR, @start, 103) and CONVERT(VARCHAR, @end, 103)";
cmd10.Parameters.AddWithValue("@start", "" + iii + "/01/2019");
cmd10.Parameters.AddWithValue("@end", "" + iii + "/30/2019");
string result = cmd10.ExecuteScalar().ToString();
con.Close();
return result;
}
now for test
现在进行测试
lbl1.Text = GetCountArchiveByMonth(**7**).ToString(); // here use any number other than (**2**)
**
**
because of check
**February**is maxed 28days,
因为支票
**February**最多28天，
**
**
