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


#4

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.


#5

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.