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

Question

Any Reason why this keeps happening even though the query is valid?... My code is below :)

Select Cdf_Code_Tx, ProjectNo, Convert (Date, [Last Call Back],103) As LastCallBack, (CDF_New_des1_Tx+CDF_New_des2_Tx) As Description
From Products
Inner Join SpecDetails
On CDF_Code_Tx = ProductRef
Inner Join Projects2
On ProjectNo = PID
WHERE  [Last Call Back] = (SELECT MAX(Convert (Date, [Last Call Back], 103))FROM Projects2)
Order By Convert (Date, [Last Call Back], 103)

When i run this , this error appears!

Msg 241, Level 16, State 1, Line 1 Conversion failed when converting date and/or time from character string.

Needing guidance & Thank you in advance.

Kindly help us posting exact error to help you added table structure info would be cheery on cake. — Vishwanath Dalvi
– Vishwanath Dalvi, Commented Nov 20, 2013 at 9:28
It seems like [Last Call Back] is a varchar column that really should be a date column. What are the values there (=any non-date values)? — Hans Keﬆing
– Hans Keﬆing, Commented Nov 20, 2013 at 9:30
Can you check some sample data on [Last Call Back] column? Or you can use ISDATE to check the column contains correct date values — huMpty duMpty
– huMpty duMpty, Commented Nov 20, 2013 at 9:33

Upendra Chaudhari · Accepted Answer · 2013-11-20 09:59:27Z

1

I think you need to check data for [Last Call Back] column in your table. Check that all data are valid for date conversion or not. I think some string are not valid to convert it into date. So you need to check it by ISDate() function. ISDDate() function will work based on your language and culture like below.

SET LANGUAGE british
SELECT ISDATE('2013-11-20')   /* will return 0 */

SET LANGUAGE us_english
SELECT ISDATE('2013-11-20')   /* will return 1 */

For example, If your column have format like below then it will give error

SELECT CONVERT(DATE, '2013-11-20', 103)

answered Nov 20, 2013 at 9:59

Upendra Chaudhari

6,5515 gold badges28 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

code save · Accepted Answer · 2013-11-20 09:58:55Z

1

I think Your Problem is Here Because I seem [Last Call Back] is Varchar() datatype when you convert varchar datatype is date and compare with varchar then you got this error Please you should change Both condition in Same Datatype

WHERE  [Last Call Back] = (SELECT MAX(Convert (Date, [Last Call Back], 103))FROM Projects2)

answered Nov 20, 2013 at 9:58

code save

1,1061 gold badge9 silver badges15 bronze badges

1 Comment

oerkelens Over a year ago

That was my first thought when reading the query as well. Why on earth convert a column in any format to something else, and then compare it with the original column format?

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related