如何删除“转化失败”来自此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 被SQL拒绝，因为它包含该格式的错误日期，或者SQL假设它是 mm-dd-yyyy 格式并因此转换它。 br />
当您在文本字段中存储日期时，您总是会在以后存储问题 - 因为错误的数据或错误的转换 将 发生。



更改数据库：使用DATETIME列存储出生日期，一大堆问题就会消失。其他任何东西都是一个重大设计错误的克服，会再一次又一次地咬你。

Simple: don't store dates in text fields.
What is happening is that the feedback column, containing a date in the form dd-mm-yyyy is being rejected by SQL because it either contains a bad date in that format, or SQL is assuming it's in mm-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屋！