How do I remove "conversion failed" error from this SQL query?

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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.

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)

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

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

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.

Posted 6-Jan-17 19:22pm

Varun Sood

Updated 8-Jan-17 1:52am

v4

Add a Solution

2 solutions

Solution 2

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.

Posted 6-Jan-17 20:53pm

OriginalGriff

Comments

Varun Sood 8-Jan-17 1:10am

Thanks for your reply!

I know storing dates in VarChar columns is not a great way. However, the system requirements demand such a structure. Actually, the column feedback contains a variety of answers of different types like integers, dates and strings. I also think the date format is the culprit. I will try to modify the web services to accept dates in ISO format yyyymmdd and let you know.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2017-01-06T20:49:00

Solution 1

Try explicitly defining the format for conversion:

SQL

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)

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.

EDIT:

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.

Posted 6-Jan-17 20:49pm

Wendelius

Updated 6-Jan-17 22:28pm

v3

Comments

Varun Sood 8-Jan-17 1:12am

The format 105 did not work for me. I am now trying to use the ISO format 112 and modify the values accordingly. Will let you know if that works.

Thanks for giving me a direction.

Varun Sood 8-Jan-17 1:39am

I have updated the question. Please look at it now whenever u r free.

Wendelius 8-Jan-17 3:34am

Few things:
- What error did you get with format 105? 105 is for dd-mm-yyyy so could it be that the data isn't in this format?
- what error do you get when the WHERE clause is present?

Varun Sood 8-Jan-17 4:07am

I got the same error:

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

I also tried updating the data to yyyymmdd format and using the format 112, but, got the same error.

Wendelius 8-Jan-17 4:26am

Did I understand correctly that this field is also used for other data than dates. So other rows may have values that are not convertible to dates?

If that is true, then the problem is that when you run the query, all rows in FeedbackTable need to be convertible to date. If even a single row contains a text value or something else, the whole query fails with conversion error.

If this is the case then you would need to add criteria in WHERE clause eliminating rows that are not convertible. Perhaps you already have a field defining the type of feedback and you could use that?

Varun Sood 8-Jan-17 4:39am

Yes you got that right. The column has data of other types. I have actually filtered the rows containing only date data using a WHERE clause. That is why I mentioned "a trimmed-down version" of my query. You are right when you say that I have defined a field defining the type of feedback.

I get the error only when I use WHERE (Age >= 25) AND (Age <= 34) in the query, which shows that the problem lies in this WHERE clause.

Wendelius 8-Jan-17 4:58am

But if you use the where clause in the outer query, it does not eliminate invalid rows from the inner query. So any row in the original table may cause the problem unless you place proper condition in the where clause of the inner query.

What comes to the age condition, as far as I can see the outer where clause is fine, it just restrict some of the rows the inner query returns.

Varun Sood 8-Jan-17 7:54am

I have already done just exactly what you said. I've updated the original question. Kindly see the update at the bottom of the question where I have included the full query as-is.

Wendelius 8-Jan-17 8:04am

So the data type is stored in a different table from the feedback? This could cause the problem.

If you run the following query

SELECT DISTINCT Feedback 
FROM  FeedbackTable
WHERE (FeedbackTable.ClientId = 1)
AND (Feedback != '-')

Do you see rows that are not dates? If you do, these are potential conversion problems.

Then what happens if you add condition
AND ISDATE(Feedback) = 1
This requires that the format in the data matches your general date setting.

Varun Sood 8-Jan-17 8:14am

AND ISDATE(Feedback) = 1 did the trick. How could I be so dumb. Probably working for long continuously spoils your mind :)

You are a life-saver. Thanks a ton!

Wendelius 8-Jan-17 8:17am

Glad you got it solved :)

But I have to say that you could re-think the structure. I understand that the same table is used for different kinds of answer but still why store them in a single column. Could you modify the structure and have separate columns for separate data types?

As an example the feedbacktable could contain columns like
- feedback_date date
- feedback_int int
- feedback_string varchar(100)

This way you could always work with correct types and no worries from conversions :)

Good luck!

Varun Sood 8-Jan-17 8:21am

Thanks for your suggestions. I will give this a thought!