"Conversion failed" Error in a CTE

Transact-SQL
, , ,
1

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.

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.

2

Try "telling" the db which date format to expect in the convert functions - like this:

WITH AgeCTE
  AS (SELECT CASE
                WHEN DATEADD(yy, DATEDIFF(yy, CONVERT(DATETIME, Feedback, 103), FeedbackDate), CONVERT(DATETIME, Feedback, 103)) < FeedbackDate
                THEN DATEDIFF(yy, CONVERT(DATETIME, Feedback, 103), FeedbackDate)
                ELSE DATEDIFF(yy, CONVERT(DATETIME, Feedback, 103), FeedbackDate) - 1
             END AS Age
        FROM FeedbackTable
    )
SELECT COUNT(*) AS [25To34]
  FROM AgeCTE
 WHERE (Age >= 25)
   AND (Age <= 34)
3

Thanks. I had already tried that. I solved that by including a check in the WHERE clause:

ISDATE(ColumnName) = 1

Thanks Anyway,

4

Right, so you are filtering out some potential results. Maybe feedback field could be a date field instead of varchar

5

Can you please post some sample data that will cause the error you are getting?

6

I solved that by including a check in the WHERE clause:

ISDATE(ColumnName) = 1

Thanks Anyway.