从字符串转换日期和/或时间时转换失败.T语言 [英] Conversion failed when converting date and/or time from character string. T-Sql
问题描述
我的数据库中有一个名为ref003的varchar列,它存储如下所示的日期时间
I have a varchar column in my database called ref003 which stores datetime like the below
2021-04-04 20:01:03
此处,日期格式类似于 yyyy-MM-DD
Here, date format is like yyyy-MM-DD
当我执行下面的选择查询时,我得到了错误
When I execute the below select query I am getting error
SELECT *
FROM FileIndex
WHERE CAST(CONVERT(CHAR(30), CONVERT(DATETIME, Ref003, 105), 101) AS DATE)
BETWEEN CAST(CONVERT(CHAR(10), CONVERT(DATETIME, '01-01-2021', 105), 101) AS DATE)
AND CAST(CONVERT(CHAR(10), CONVERT(DATETIME, '31-12-2021', 105), 101) AS DATE)
错误是
消息241,级别16,状态1,第5行转换时转换失败字符串中的日期和/或时间.
Msg 241, Level 16, State 1, Line 5 Conversion failed when converting date and/or time from character string.
这是什么问题,我该如何解决?
What is the problem here and how can I solve this problem?
推荐答案
首先,对于转换,您需要转换为 VARCHAR
,而不是 DATE
.请注意以下表达式:
First, for the conversion you need to convert to VARCHAR
, not DATE
. Note this expression:
CONVERT(VARCHAR(10), CAST(<your date value> AS DATE), 20)
考虑到这一点,您可以像这样清理查询:
With that in mind, you can clean your query up like so:
--==== Easily consumable sample data
DECLARE @thetable TABLE (someid INT, thedate DATETIME);
INSERT @thetable
VALUES(1,'2021-04-04 20:01:03'),(2,'2021-06-04 22:01:05'),(1,'2021-04-29 10:31:11');
--==== Solution
SELECT t.*, FormattedDate = fmt.Dt
FROM @thetable AS t
CROSS APPLY (VALUES(CONVERT(VARCHAR(10), CAST(t.thedate AS DATE), 20))) AS fmt(Dt)
WHERE t.thedate BETWEEN '20210401' AND '20210501';
返回:
someid thedate FormattedDate
----------- ----------------------- -------------
1 2021-04-04 20:01:03.000 2021-04-04
1 2021-04-29 10:31:11.000 2021-04-29
这篇关于从字符串转换日期和/或时间时转换失败.T语言的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!