CODEWITHSUNDEEP
sqlsql-server
LisaM
LisaM

Reputation: 23

another Conversion failed when converting datetime from character string

SELECT Asset.AssetID, AnsMaint.Answer, Convert(datetime, AnsMaint.Answer) as maintasdate
FROM Asset INNER JOIN 
AssetAnswer AnsMaint ON AnsMaint.AssetID = Asset.AssetID INNER JOIN  
AssetField FldMaint ON FldMaint.AssetFieldID = AnsMaint.AssetFieldID 
WHERE FldMaint.FieldText = 'Maint. Agreement Term' 
AND ISDATE(AnsMaint.Answer) = 1 
AND Convert(datetime, AnsMaint.Answer) < DateAdd(d, 145, GetDate())

I get the error on the last part of the AND. If I comment the AND out, it works fine. My dates in the DB happen to be 10/10/2012 and are valid. IsDate should weed out anything that is not valid.

In DB the results (when I comment out the last line). I'm completely stumped.

106 10/10/2012  2012-10-10 00:00:00.000
115 10/10/2012  2012-10-10 00:00:00.000

MORE interesting tidbits. If I change the last AND line to 

AND DateAdd(d, cast(Asset.MaintenanceFreq as int), Convert(datetime, AnsMaint.Answer)) < DateAdd(d, 45, GetDate())

it works. If I take out the 2nd parameter (the cast as int) and replace it with a number or a zero, it gives me the same error.

I'm stumped. Any help would be so much appreciated!

Oh, AssetMaint.Answer is a varchar field in the DB nothing I can do about that.

Upvotes: 2

Views: 2137

Answers (2)

Richard Deeming
Richard Deeming

Reputation: 31198

From the description, it sounds like the Answer column contains values for some records which can't be converted to a date, and SQL is choosing an execution plan which evaluates the CONVERT before the ISDATE.

Try using a CASE statement for the conversion instead:

WHERE FldMaint.FieldText = 'Maint. Agreement Term' 
AND CASE ISDATE(AnsMaint.Answer) 
   WHEN 1 THEN Convert(datetime, AnsMaint.Answer, 103) 
END < DateAdd(d, 145, GetDate())

Upvotes: 1

Tony Hopkinson
Tony Hopkinson

Reputation: 20320

It's a guess , but I'd say it was mm/dd/yyyy versus dd/mm/yyyy. e.g. 10/10 is okay, but 20/10 or 10/20 might not be.

add the style parameter to the converts e.g. 

Convert(datetime, AnsMaint.Answer,103)

You'll have to look up which one based on what ever date format is in your mis-typed field.

Upvotes: 0

Related Questions