Comments by Varun Sood

Varun Sood 8-Jan-17 8:21am View

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

Varun Sood 8-Jan-17 8:14am View

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!

Varun Sood 8-Jan-17 7:54am View

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.

Varun Sood 8-Jan-17 4:39am View

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.

Varun Sood 8-Jan-17 4:07am View

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.

Varun Sood 8-Jan-17 1:39am View

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

Varun Sood 8-Jan-17 1:35am View

Deleted

UPDATE:

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...

Varun Sood 8-Jan-17 1:12am View

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:10am View

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.

Comments by Varun Sood (Top 9 by date)