Varun Sood Ответов: 2

Как удалить ошибку "conversion failed" из этого SQL-запроса?


Вот урезанная версия запроса SQL Server 2012, который я использую для получения всех пользователей в возрасте от 25 до 34 лет. В моем столе,

1. колонка "обратная связь" имеет тип VarChar и хранит дату рождения пользователя в виде VarChar в формате dd-mm-yyyy.

2. столбец " FeedbackDate "имеет тип DateTime и в основном является датой, когда пользователь сообщил мне свой DOB в форме" обратной связи", которую я храню в столбце" обратная связь".

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)


При выполнении этого запроса я получаю следующую ошибку:

Conversion failed when converting date and/or time from character string.


ОБНОВЛЕНИЕ:

Просто чтобы избежать какой-либо путаницы, вот полный SQL-запрос, который я использую:

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)


Пожалуйста, помогите.

С уважением,
Варун

Что я уже пробовал:

Я попытался привести возраст в предложении WHERE к int. Но и это не работает.

Кроме того, когда я пытаюсь выполнить внутренний запрос, он выполняется успешно, что показывает, что формат дат не является проблемой до тех пор, пока этот внутренний запрос не будет выполнен.

Например, когда я выполняю следующий запрос:

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
и т.д...

Даже весь запрос с CTE работает, если я опущу последнее предложение WHERE. В частности, если я удалю раздел
WHERE (Age >= 25) AND (Age <= 34)
и выполните следующий запрос:

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


Я получаю следующий вывод:

25то34
------
9

Я не могу понять, в чем проблема с предложением WHERE. Я где-то читал в интернете, что это может быть короткое замыкание, но не могу понять, как. Любая помощь будет оценена по достоинству.

2 Ответов

Рейтинг:
15

Wendelius

Попробуйте явно определить формат для преобразования:

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 не обязательно является хорошей идеей. Если вы используете имена столбцов в своей программе, динамическое именование столбцов в результирующем наборе затрудняет ссылку на них в коде.


Varun Sood

Формат 105 не работал для меня. Теперь я пытаюсь использовать формат ISO 112 и соответствующим образом изменить значения. Я дам вам знать, если это сработает.

Спасибо, что дали мне направление.

Varun Sood

Я обновил вопрос. Пожалуйста, посмотрите на него сейчас, когда вы свободны.

Wendelius

Несколько вещей:
- Какую ошибку вы получили с форматом 105? 105-это для ДД-ММ-гггг, так может быть, данные не в этом формате?
- какую ошибку вы получаете, когда присутствует предложение WHERE?

Varun Sood

Я получил ту же ошибку:

Преобразование не удалось при преобразовании даты и / или времени из символьной строки.

Я также попытался обновить данные в формате yyyymmdd и использовать формат 112, но получил ту же ошибку.

Wendelius

Правильно ли я понял, что это поле также используется для других данных, кроме дат? Таким образом, другие строки могут иметь значения, которые не конвертируются в даты?

Если это так, то проблема заключается в том, что при выполнении запроса все строки в FeedbackTable должны быть конвертированы на сегодняшний день. Если даже одна строка содержит текстовое значение или что-то еще, весь запрос завершается ошибкой преобразования.

Если это так, то вам нужно будет добавить критерии в предложение WHERE, исключающие строки, которые не являются конвертируемыми. Возможно, у вас уже есть поле, определяющее тип обратной связи, и вы могли бы его использовать?

Varun Sood

Да, вы все правильно поняли. Столбец содержит данные других типов. Я фактически отфильтровал строки, содержащие только данные о дате, используя предложение WHERE. Вот почему я упомянул "урезанную версию" моего запроса. Вы правы, когда говорите, что я определил поле, определяющее тип обратной связи.

Я получаю ошибку только тогда, когда использую WHERE (Age >= 25) и (Age <= 34) в запросе, который показывает, что проблема заключается в этом предложении WHERE.

Wendelius

Но если вы используете предложение where во внешнем запросе, оно не устраняет недопустимые строки из внутреннего запроса. Таким образом, любая строка в исходной таблице может вызвать проблему, если вы не поместите правильное условие в предложение where внутреннего запроса.

Что касается условия возраста, то, насколько я вижу, внешнее предложение where в порядке, оно просто ограничивает некоторые строки, возвращаемые внутренним запросом.

Varun Sood

Я уже сделал в точности то, что вы сказали. Я обновил первоначальный вопрос. Пожалуйста, посмотрите обновление в нижней части вопроса, где я включил полный запрос как есть.

Wendelius

Таким образом, тип данных хранится в другой таблице, отличной от обратной связи? Это может вызвать проблему.

Если вы выполните следующий запрос
SELECT DISTINCT Feedback
FROM FeedbackTable
WHERE (FeedbackTable.ClientId = 1)
AND (Feedback != '-')

Видите ли вы строки, которые не являются датами? Если вы это сделаете, это потенциальные проблемы с конверсией.

Тогда что произойдет, если вы добавите условие
AND ISDATE(Feedback) = 1
Для этого требуется, чтобы формат данных соответствовал вашей общей настройке даты.

Varun Sood

И ISDATE (обратная связь) = 1 сделал свое дело. Как я мог быть таким тупым? Вероятно, работа в течение длительного времени постоянно портит ваш ум :)

Ты спасаешь мне жизнь. Огромное спасибо!

Wendelius

Рада, что вы получили его решена :)

Но я должен сказать, что вы могли бы переосмыслить структуру. Я понимаю, что одна и та же таблица используется для разных видов ответов, но все же зачем хранить их в одном столбце. Не могли бы вы изменить структуру и иметь отдельные столбцы для отдельных типов данных?

В качестве примера feedbacktable может содержать такие столбцы, как
- дата обратной связи
- feedback_int инт
- feedback_string varchar(100)

Таким образом, вы всегда можете работать с правильными типами и не беспокоиться о конверсиях :)

Удачи!

Varun Sood

Спасибо за ваши предложения. Я подумаю об этом!

Рейтинг:
1

OriginalGriff

Просто: не храните даты в текстовых полях.
Что происходит, так это то, что feedback столбец, содержащий дату в форме dd-mm-yyyy отклоняется SQL, потому что он либо содержит плохую дату в этом формате, либо SQL предполагает, что она находится в mm-dd-yyyy отформатируйте и преобразуйте его таким образом.
Когда вы храните даты в текстовых полях, вы всегда накапливаете проблемы для себя позже - из-за плохих данных или ложных конверсий будет происходить.

Измените свою базу данных: используйте столбец DATETIME для хранения даты рождения, и целая куча проблем исчезнет. Все остальное - это Клудж вокруг крупной ошибки дизайна и будет кусать вас снова, и снова, и снова.


Varun Sood

Спасибо за ваш ответ!

Я знаю, что хранение дат в Столбцах VarChar-не самый лучший способ. Однако системные требования требуют такой структуры. На самом деле, обратная связь столбца содержит множество ответов различных типов, таких как целые числа, даты и строки. Я также думаю, что виноват формат даты. Я постараюсь изменить веб-сервисы, чтобы они принимали даты в формате ISO yyyymmdd, и дам вам знать.