another Conversion failed when converting datetime from character string

Question

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.

I would say from the SQL dialect, this is SQL Server, but the OP could help by clarifying that. — Andrew
– Andrew, Commented Oct 23, 2012 at 16:25
"I get the error on the last part of the AND". What's the error message? — Joachim Isaksson
– Joachim Isaksson, Commented Oct 23, 2012 at 16:26
error message is: Conversion failed when converting datetime from character string. — LisaM
– LisaM, Commented Oct 23, 2012 at 16:28
A note about ISDATE() 'weeding out' non-date values - SQL doesn't really do short-circuit logic like imperative languages; the optimizer is allowed to rearrange things as it sees fit. — Clockwork-Muse
– Clockwork-Muse, Commented Oct 23, 2012 at 16:35

Richard Deeming · Accepted Answer · 2012-10-23 16:35:32Z

1

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())

answered Oct 23, 2012 at 16:35

Richard Deeming

31.5k11 gold badges86 silver badges167 bronze badges

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

1 Comment

LisaM Over a year ago

Brilliant! So SQL can "choose" what order to do the execution plan? That is a bit maddening.

Tony Hopkinson · Accepted Answer · 2012-10-23 16:32:38Z

0

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.

answered Oct 23, 2012 at 16:32

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Collectives™ on Stack Overflow

another Conversion failed when converting datetime from character string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related