This is just an example:

SELECT * FROM A.B where B_DATE='19-05-2016 AM';

I always get an error:

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

I have looked at trying to use CONVERT, but I can't see any format that matches the datetime format I have above. Any advice on how I can rectify this?

1 Answer 1


If you are using a 'literal date' you need to type the format differently. If you are using a variable or field you will need to use substring and parse into the correct format.

I have a DateTime2 field in a table and the following works, similar to your query but formatted differently:

SELECT * FROM Table_1 where CreatedOn='2016-05-19T00:00:00.0000000';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.