如何删除“转化失败”来自此SQL查询的错误? [英] How do I remove "conversion failed" error from this SQL query?
问题描述
这是我用来获取年龄在25到34岁之间的所有用户的SQL Server 2012查询的精简版本。在我的表格中,
1.反馈栏目的类型为VarChar,并以dd-mm-格式将用户的出生日期存储为VarChar yyyy。
2.FeedbackDate栏目的类型为DateTime,基本上是用户以反馈的形式告诉我他的DOB的日期我存储在反馈栏中。
Here is a trimmed-down version of a SQL Server 2012 query I am using to get all users with age between 25 and 34 years. In my table,
1. The column "Feedback" is of type VarChar and stores the date of birth of a user as a VarChar in the format dd-mm-yyyy.
2. The column "FeedbackDate" is of type DateTime, and is basically the date on which the user told me his DOB in the form of a "feedback" which I store in the "Feedback" column.
WITH AgeCTE AS
(
SELECT
CASE
WHEN DATEADD(yy, DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate), CONVERT(DATETIME, Feedback)) < FeedbackDate
THEN DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate)
ELSE DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate) - 1
END AS Age
FROM FeedbackTable
)
SELECT COUNT(*) AS [25To34] FROM AgeCTE WHERE (Age >= 25) AND (Age <= 34)
执行此查询时,我我收到以下错误:
On executing this query, I am getting the following error:
Conversion failed when converting date and/or time from character string.
更新:
为了避免任何混淆,这里是我正在使用的完整SQL查询:
UPDATE:
Just to avoid any confusion, here is the full SQL Query I am using:
WITH AgeCTE AS
(
SELECT
CASE
WHEN DATEADD(yy, DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate), CONVERT(DATETIME, Feedback)) < FeedbackDate
THEN DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate)
ELSE DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate) - 1
END AS Age
FROM
FeedbackTable
INNER JOIN
FeedbackDetailsTable ON FeedbackDetailsTable.FeedbackId = FeedbackTable.FeedbackId
INNER JOIN
QuestionsTable ON QuestionsTable.QuestionId = FeedbackDetailsTable.QuestionId
INNER JOIN
QuestionTypesTable ON QuestionTypesTable.QuestionTypeId = QuestionsTable.QuestionTypeId
WHERE
(FeedbackTable.ClientId = 1)
AND (QuestionTypeName = 'DateOfBirth')
AND (Feedback != '-')
)
SELECT COUNT(*) AS [18To24] FROM AgeCTE WHERE (Age >= 25) AND (Age <= 34)
请帮助。
问候,
Varun
我尝试过:
我已经尝试将WHERE子句中的Age转换为int。但这也不起作用。
另外,当我尝试执行内部查询时,它会成功执行,这表明日期的格式不是问题,直到这个内部查询被执行。
例如,当我执行以下查询时:
Kindly help.
Regards,
Varun
What I have tried:
I have tried casting Age in the WHERE clause to an int. But that does not work either.
Also, when I try to execute the inner query, it executes successfully which shows that the format of dates is not a problem till this inner query is executed.
For example, when I execute the following query:
SELECT
CASE
WHEN DATEADD(yy, DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate), CONVERT(DATETIME, Feedback)) < FeedbackDate
THEN DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate)
ELSE DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate) - 1
END AS Age
FROM FeedbackTable
我得到的输出为:
年龄
---
23
33
35
8
等...
如果省略最后一个WHERE子句,即使是CTE的整个查询也能正常工作。具体来说,如果我删除
I get an output as:
Age
---
23
33
35
8
etc...
Even the entire query with the CTE works if I omit the last WHERE clause. Specifically, if I remove the section
WHERE (Age >= 25) AND (Age <= 34)
部分并执行以下查询:
and execute the following query:
WITH AgeCTE AS
(
SELECT
CASE
WHEN DATEADD(yy, DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate), CONVERT(DATETIME, Feedback)) < FeedbackDate
THEN DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate)
ELSE DATEDIFF(yy, CONVERT(DATETIME, Feedback), FeedbackDate) - 1
END AS Age
FROM FeedbackTable
)
SELECT COUNT(*) AS [25To34] FROM AgeCTE
我得到以下输出:
25To34
------
9
我无法理解WHERE子句的问题。我在互联网上的某处读到它可能是短路的,但是,我无法弄清楚如何。任何帮助将不胜感激。
I get the following output:
25To34
------
9
I am not able to understand what the problem is with the WHERE clause. I read somewhere on the internet that it could be short-circuiting, but, I cannot figure it out how. Any help would be appreciated.
推荐答案
尝试明确定义转换格式:
Try explicitly defining the format for conversion:
WITH AgeCTE AS
(
SELECT
CASE
WHEN DATEADD(yy, DATEDIFF(yy, CONVERT(DATETIME, Feedback, 105), FeedbackDate), CONVERT(DATETIME, Feedback,105)) < FeedbackDate
THEN DATEDIFF(yy, CONVERT(DATETIME, Feedback, 105), FeedbackDate)
ELSE DATEDIFF(yy, CONVERT(DATETIME, Feedback, 105), FeedbackDate) - 1
END AS Age
FROM FeedbackTable
)
SELECT COUNT(*) AS [25To34] FROM AgeCTE WHERE (Age >= 25) AND (Age <= 34)
总的来说,更改数据类型以便反馈到日期是有意义的,这样你就不会必须不断进行转换。
编辑:
作为旁注,修改列名称关于WHERE子句中的标准不一定是个好主意。如果在程序中使用列名,则动态命名结果集中的列会使代码中的列更难引用。
In overall it would make sense to change the data type for feedback to date so that you wouldn't have to make conversions constantly.
As a side note, modifying the column name based on the criteria in WHERE clause is not necessarily a good idea. If you use column names in your program, dynamically naming the columns in the result set makes it harder to reference them in the code.
简单:不在文本字段中存储日期。
正在发生的是反馈
列,其中包含dd-mm-yyyy $ c形式的日期$ c>被SQL拒绝,因为它包含该格式的错误日期,或者SQL假设它是
mm-dd-yyyy
格式并因此转换它。 br />
当您在文本字段中存储日期时,您总是会在以后存储问题 - 因为错误的数据或错误的转换 将 发生。
更改数据库:使用DATETIME列存储出生日期,一大堆问题就会消失。其他任何东西都是一个重大设计错误的克服,会再一次又一次地咬你。
Simple: don't store dates in text fields.
What is happening is that thefeedback
column, containing a date in the formdd-mm-yyyy
is being rejected by SQL because it either contains a bad date in that format, or SQL is assuming it's inmm-dd-yyyy
format and converting it thus.
When you store dates in text fields, you always store up problems for yourself later - because bad data or false conversions will occur.
Change your database: use a DATETIME column to store the birth date and a whole pile of problems will go away. Anything else is a kludge round a major design mistake and will bite you again, and again, and again.
这篇关于如何删除“转化失败”来自此SQL查询的错误?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!