SQLTeam.com | Weblogs | Forums

Query to find the error field/data


#1

Please help me to find the exact data
which shows the error :

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

Date field having record like '01/04/2013 03:24:50'


#2
SELECT KeyColumn1, KeyColumn2, DateColumn
FROM YourTable
WHERE IsDate(DateColumn) = 0
      AND DateColumn IS NOT NULL

#3

By the by, this is not a good idea. SQL will treat that format as "ambiguous". If something changes SQL may treat it as d/m/y or m/d/y. You should only use "yyyymmdd" for "string dates" as SQL will always treat 8-digit string-dates as being in that format.

"something changes" can be a setting on the server, or the Language that the currently connected user has selected. American and British English, or French, will parse that date differently and of course 12/31/2016 and 31/12/2016 are very different.

If SQL is parsing the date wrongly for you then you can use

SET DATEFORMAT DMY -- or MDY etc :)

to force it to parse the 3 date elements in the right order