Date Conversion error

Question

This is just an example:

SELECT * FROM A.B where B_DATE='19-05-2016 12.00.00.000000000 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?

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';