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.