SQLTeam.com | Weblogs | Forums

Query to find the error field/data


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'

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


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


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


I tried a program in oracle for my students. But it shows some error. I can't find out the error. Please Anybody help me?
SQL> declare cursor flower
2 is
3 select * from hair;
4 newsalary flower%rowtype;
5 begin
6 open flower;
7 loop fetch flower into newsalary;
8 exit when flower %notfound;
9 if newsalary.salary>2500 and newsalary.salary<3000 then
10 newsalary:=salary+salary*10/100;
11 update hair SET salary=newsalary where salary>2500 and salary<3000;
12 end loop;
13 close flower;
14 end;
15 /

This is my program. Please help me.


Check your values of the string when you are trying to convert them into string. Either there can be incorrect values or they may be incorrectly formatted.