1 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Overflow errors on dates

I'm getting overflow errors in three of the new XML reports, all from the Statistics menu and all associated with dates.

Number of records in a survey (CountSurveyRecords) and Determination dates before sample date (ProblemDeterminationDates) both give the error message "Adding a value to a 'datetime' column caused overflow".

Count of records made within date range V07-12 gives the message "arithmetic overflow error converting expression to data type 'datetime'".

These reports are working for other people so could the problem be that I have some duff dates in my database somewhere? If so, any ideas on  what I should be looking for?

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

2 Reply by SimonWood

  • SimonWood
  • Veteran
  • Offline
  • Registered: 09-09-2008
  • Posts: 63

Re: Overflow errors on dates

Hi Janet,

The CountSurveyRecords report appears to have an issue if you have any surveys which have a 'Records Allowed From' date from pre 1700 ish.
There's a similar issue with the ProblemDeterminationDates report but regarding determination & sample dates.  Does this sound right based on your data?

Thanks,

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

SimonWood's Website

3 Reply by Janet Simkin

  • Janet Simkin
  • Janet Simkin
  • Veteran
  • Offline
  • From: Northumberland
  • Registered: 10-05-2006
  • Posts: 260

Re: Overflow errors on dates

Hi Simon,

Yes, quite right. All our surveys were set to allow records from 1650, and changing them to 1700 didn't help. The Count of Records made within Date Range report doesn't allow dates before 14/9/1752 so I tried changing all the surveys to allow from 1760. Now the CountSurveyRecords report runs, so that's progress, but it only includes surveys which are run by an individual and not those run by an organisation (such as the British Lichen Society or a Local Records Centre) so it's still not much use!

The other two reports still fail because we have a lot of records in our database with date ranges starting either 1700- or 1735- . Is 1752 a fundemental limit? If so I can make a global change to our database, but as there would be more than 100k taxon occurences affected, together with their events, samples and determinations, I would first like to be sure that there is no other way. A limit of 1752 would also give us on-going problems in handling the oldest lichen records, as there were several active recorders before that date.

Janet

Janet Simkin
British Lichen Society

Janet Simkin's Website

4 Reply by SimonWood (edited by SimonWood 29-09-2008 08:03:45)

  • SimonWood
  • Veteran
  • Offline
  • Registered: 09-09-2008
  • Posts: 63

Re: Overflow errors on dates

Hi Janet,

There is an issue in the reports in that the dates they're reporting are limited in value by SQL standard behaviour to between 1753-01-01 and 9999-12-31.   Rather than changing all your data I'd suggest the reports be modified to cope properly with historic dates.  I'll recommend this.  In the meantime I've emailed you a version of the CountSurveyRecords report which copes with all dates plus it also returns organisations and not just individuals.  The ProblemDeterminationDates would require a bit more tweaking to get it working for older dates.

Thanks,
Simon

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

SimonWood's Website

5 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Overflow errors on dates

I am not sure what type of date processing you are trying to do (I can't find the CountSurveyRecords report) but if your system contains dates prior to 1753 you will need to accommodate them. I believe the user defined functions developed for JNCC by Mike Weideli do, e.g. dbo.LCReturnVagueDateShort(VagueDateStart, VagueDateEnd, VagueDateType) - see his documentation for more details http://forums.nbn.org.uk/viewtopic.php?pid=3113#p3113. There was a useful write up on dates, including the 1753 issue, on the Wiki so I hope we will have access to that again soon.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687