Question Sql Width Column Value Exceeding Its Max Length Or Precision

[BigSlick]

Hi All,

OpenEdge 10.2BSP07 64bit Windows 64bit.

An SQL user is having an issue and it getting "Column value exceeding its max length or precision". Now i'm aware of dbtool and although it takes an age it doesn't report on the column that is providing the grief. The column in question is a date!

From my understanding date data types are SQL formatted to 4 (not sure why?) and all dates seem this way. So unsure why the user is getting this error.

I've been through a lot of the data (there are over 2 billion records) and i can't see anything that stands out; either that data is unassigned or in dd/mm/yyyy format (sorry America!)

Could anybody shed any light on this?

Apologies for the vagueness but i'm not had much dealings with SQL.

 

[Cringer]

Not got experience with this either, but if you don't get an answer here then get over to https://community.progress.com/?Redirected=true as there are a lot of folks there who can help.

 

[Osborne]

Never come across this problem with a date field. Does trying something like this help?:

FOR EACH rec NO-LOCK:
    IF LENGTH(STRING(rec.recdate)) > 8 THEN
       DISPLAY rec.
END.

 

[BigSlick]

Hi Osborne,

I'm in the middle of running that exact query, its 200 million of 2 billion down so far! - I'm hoping it is data!

Thanks.

 

[TheMadDBA]

OpenEdge ABL supports dates that are out of bound for SQL.

ABL Date Ranges: 1/1/32768 B.C. to 12/31/32767 A.D.
SQL Date Ranges: Year: 1 to 9999; Month: 1 through 12; Date: 1 through the last day of the month

It is possible that you have junk dates below or above the SQL limits.

 

[TomBascom]

It is also possible that a "date field" is not really a date field. Sometimes developers do silly things like create "dates" that are character or decimal or some other weirdo thing.

 

[BigSlick]

Hi guys,

Thanks for the responses.

It is definitely a date format 99/99/9999.

Maybe as TheMadDBA suggests, the dates are out of range. Maybe were inputting 20015 rather than 2015 every now and again.