将varchar数据类型转换为datetime数据类型导致SQL查询中的值超出范围 [英] Conversion of a varchar data type to a datetime data type resulted in an out-of-range value in SQL query
问题描述
我有一个表,该表的列存储日期和时间。我需要编写查询以仅从该列获取日期,
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:
将varchar数据类型转换为日期时间数据类型会导致值超出范围。
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
该列中的数据为datetime,例如:2012-06-18 12:08:04.000
,因此我只需要提取日期并删除时间
请注意,[Logintime]列是数据时间格式
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
推荐答案
尝试 ISDATE()函数。如果为1,请选择有效日期。如果0选择无效的日期。
Try ISDATE()
function in SQL Server. If 1, select valid date. If 0 selects invalid dates.
SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)
FROM AuditTrail
WHERE ISDATE(LoginTime) = 1
- 单击此处以查看结果
- Click here to view result
编辑:
根据您的更新,我只需要提取日期并删除时间,那么您只需使用内部 CONVERT
As per your update i need to extract the date only and remove the time, then you could simply use the inner CONVERT
SELECT CONVERT(VARCHAR, LoginTime, 101) FROM AuditTrail
或
SELECT LEFT(LoginTime,10) FROM AuditTrail
中选择左(LoginTime,10)
编辑2:
EDIT 2 :
错误的主要原因是您在WHERE子句中的日期,即
The major reason for the error will be in your date in WHERE clause.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)
将不同于
SELECT cast(CONVERT(varchar, LoginTime, 101) as datetime)
FROM AuditTrail
where CAST(CONVERT(VARCHAR, LoginTime, 101) AS DATE) <=
CAST('18/06/2012' AS DATE)
结论
在编辑2 第一个查询尝试以 mm / dd / yyyy
格式过滤,而第二个查询尝试以 dd / mm / yyyy过滤
格式。它们中的任何一个都将失败并引发错误
In EDIT 2 the first query tries to filter in mm/dd/yyyy
format, while the second query tries to filter in dd/mm/yyyy
format. Either of them will fail and throws error
将varchar数据类型转换为日期时间数据类型会导致
in out范围值。
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
因此,请确保使用 mm / dd / yyyy
或 dd / mm / yyyy
格式,无论哪种格式都适用于您的数据库。
So please make sure to filter date either with mm/dd/yyyy
or with dd/mm/yyyy
format, whichever works in your db.
这篇关于将varchar数据类型转换为datetime数据类型导致SQL查询中的值超出范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!