Date Conversion error 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?
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';