1

Re: Arithmetic overflow error converting expression to data type int

I'm getting the following error when reporting on a polygon. It looks related to dates, specifically, the Sample Month attribute. Does John or anyone else have any clue as to what might be causing this?

Thanks,
Charles

Exception occurred in application Recorder 6 at 04/09/2008 10:56:27.
Version : 6.13.2.176

Exception path:
EOleException : Arithmetic overflow error converting expression to data type int
TExceptionPath : An error occurred executing the following SQL:
UPDATE "#REPORT_OUTPUT" SET #REPORT_OUTPUT.[Sample Month] =
    CASE WHEN SAMPLE.VAGUE_DATE_TYPE IN ('P', 'Y', 'YY', 'Y-', 'Y', 'S', 'U') THEN NULL
    WHEN SAMPLE.VAGUE_DATE_START IS NULL THEN NULL
    WHEN SAMPLE.VAGUE_DATE_END IS NULL THEN NULL
ELSE dbo.FormatDatePart(SAMPLE.VAGUE_DATE_START, SAMPLE.VAGUE_DATE_END, SAMPLE.VAGUE_DATE_TYPE, 1) END FROM (#REPORT_OUTPUT
LEFT JOIN
SAMPLE
ON #REPORT_OUTPUT.SAMPLE_KEY = SAMPLE.SAMPLE_KEY)

Last event\actions:
  TfrmFilterResult created
  TfrmFilterResult activated
  TdlgWizard created
  TdlgWizard destroyed
  TfrmFilterResult destroyed
  actReportWizard invoked
  TfrmFilterResult created
  TfrmFilterResult activated
  TdlgWizard created

Operating System : Windows XP  5.01.2600  Service Pack 3
Physical Memory available : 2,096,124 KB

DLLs loaded:
  advapi32.dll (5.1.2600.5512)
  comctl32.dll (5.82.2900.5512)
  comdlg32.dll (6.0.2900.5512)
  gdi32.dll (5.1.2600.5512)
  gdiplus.dll (5.1.3102.5512)
  HHCtrl.ocx (5.2.3790.4110)
  kernel32.dll (5.1.2600.5512)
  mpr.dll (5.1.2600.5512)
  MS5.Dll (5.0.0.12)
  MS5User.Dll (5.0.0.4)
  odbc32.dll (3.525.1132.0)
  ole32.dll (5.1.2600.5512)
  oleaut32.dll (5.1.2600.5512)
  shell32.dll (6.0.2900.5512)
  user32.dll (5.1.2600.5512)
  version.dll (5.1.2600.5512)
  winmm.dll (5.1.2600.5512)
  winspool.drv (5.1.2600.5512)

Information has been saved to the file T:\Recorder 6 Server\User Files\LastError.txt

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

2

Re: Arithmetic overflow error converting expression to data type int

Hi Charles,

There appears to be an issue with the FormatDatePart user function.  It fails for certain vague date values - in particular the 31st December of leap years.  e.g 31/12/2004 ; 31/12/2008

This report is failing because the SAMPLE.VAGUE_DATE_START value relates to one of these dates,  i.e. 31/12/2008 = 39813 ; 31/12/2004 = 38352.  Basically removing 1461 for every 4 years.

Examples:
39813    2008
38352    2004
36891    2000
35430    1996
33969    1992
32508    1988
31047    1984
29586    1980

Kind regards,

Simon Wood
[url=http://www.dorsetsoftware.com/]Dorset Software Services[/url]