1 Topic by Eleanor Knott

  • Eleanor Knott
  • Veteran
  • Offline
  • Registered: 21-01-2009
  • Posts: 93

Topic: NBN export leap year problem

I've just sent an update to the NBN gateway, and was sent back a list of records with errors.  These are all relating to leap years, where records have been entered onto Recorder with a date of February 2004, and have come out in the NBN export with a end date of 28/02/2004 instead of 29/02/2004.

This is the first time this have been picked up, although I have been supplying these datasets for the past couple of years, and they have all had the same problem.

Can the NBN export add-in be tweaked to fix this please?  Otherwise I have to manually check the exports for dodgy leap years.

Cheers
Ellie
Devon Biodiversity Records Centre

Eleanor Knott
Devon Biodiversity Records Centre

2 Reply by Eleanor Knott

  • Eleanor Knott
  • Veteran
  • Offline
  • Registered: 21-01-2009
  • Posts: 93

Re: NBN export leap year problem

Bizarrely, it only seems to affect a few leap years (1980, 2000 & 2004).  Records for February 2008 come out with an end date of 29/02/2008, and 2012, 1992 & 1988 are also fine.  I can't check 1984 or 1996 as we don't have any records for February for those years

Ellie

Eleanor Knott
Devon Biodiversity Records Centre

3 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,396

Re: NBN export leap year problem

Presumably the same problem as:

http://forums.nbn.org.uk/viewtopic.php?id=5001

and related to:

http://forums.nbn.org.uk/viewtopic.php?id=1146

http://forums.nbn.org.uk/viewtopic.php?id=2146

?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

4 Reply by TeresaF

  • TeresaF
  • Veteran
  • Offline
  • From: BTO
  • Registered: 12-05-2008
  • Posts: 172

Re: NBN export leap year problem

I've had this problem again too, for all leap years. I was told the latest version of the NBN addinfor R6 has a fix, however it can cause other issues. Perhaps that is why it only corrected certain years for you Eleanor. I don't use the NBN addin so that doesn't help me directly (I get the date and datetype using a snapshot report into SQL Server).

I'd prefer it if it could be amended directly in the way Recorder 6 stores February dates in leap years... or to make things even simpler for the NBN Gateway to use the 28th February for the vague date February for all years like Recorder does, surely that is a simpler way of representing vague dates anyway!

-----------------
Teresa Frost | Wetland Bird Survey National Organiser | BTO
Other hat  | National Forum for Biological Recording Council
(Old hats  | NBN Board, ALERC Board, CBDC, KMBRC)

5 Reply by BDeed

  • BDeed
  • Veteran
  • Offline
  • From: Bolton
  • Registered: 01-03-2011
  • Posts: 451

Re: NBN export leap year problem

I'd just like to add that we also have this issue and a fix would be appreciated. Like Teresa we also do not use the NBN Addin. Fortunately, only the one record needed correcting this time around but this may not be the case in the future.

Natural History & Biodiversity Data Enthusiast

6 Reply by Eleanor Knott

  • Eleanor Knott
  • Veteran
  • Offline
  • Registered: 21-01-2009
  • Posts: 93

Re: NBN export leap year problem

According to staff at the NBN 'The bug for leap years occurs in any version of the NBN Exchange Addin previous to version 6.19. You can download the latest version of the NBN Exchange Addin (V6.22) where the bug has been fixed and there are no longer problems with leap years (http://jncc.defra.gov.uk/page-4597). This version of the Addin needs the latest version of Recorder (http://jncc.defra.gov.uk/page-4612).

However, I must warn you that some users installed the latest version of the Recorder and the Addin and then due to setting issues, they were not able to make the Adding work at all. I would recommend that you install everything in a separate PC as a test to make sure it works.'

I haven't tried installing the latest version of the NBN Exchange add-in yet, but will give it a go at some point.

Ellie

Eleanor Knott
Devon Biodiversity Records Centre

7 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: NBN export leap year problem

The way R6 deals with leap years dates seems to be the right way to me.   Recorder holds dates as values as the number of days since  30 December 1899. The date field in SQL Server can't hold dates before 1/1/1753  so to allow  R6 to hold dates earlier than this they are  held as the number (either positive of negative) from 30 December 1899 (day 0) . This date for day zero  is a standard used in a number of programs including MS Access. 

When months are entered into R6 it uses the last day of the month for the vague_date_to_Value. So for example  'February 2000' become vague_date_ start = 36557, vague_date_end 36585, vague_date_Type O.   36557 is 1 February 2000  and 36585 is the 29 February 2000.   

There was an error in the original NBNExchange Addin in changing these numeric dates back into normal dates. This is fixed in V6.22.  Users who do there own extracts for the NBNGateway can use UDF   [dbo].[LCReturnVagueDateShort]  to return the date in the R6 vague date format. Or [dbo].[LCReturnDate] to deal with a single date field. These cope correctly with leap years.

eg SELECT dbo.LCReturnVagueDateShort(36557,36585,'O')  returns February 2000

SELECT dbo.LCReturnDate(36585,'D','F') returns 29/02/2000

To get the R6 vague date from a date in dd/mm/yyyy format you can use UDF dbo.LCtoRataDie

eg.  select dbo.LctoRatadie ('30/12/1899') returns 0


Version 6.22 of the NBNExchange Addin is a standard addin without the complexity of earlier versions. If other addins can be installed then there shouldn't be a problem with this one.  However, you do need to be on R6 version 6.22 or above for it to work.  Problems with addin installation usually seem to be down to permission issues. The addin installation will need permission to read/write  to the addin folder and also permission to update the  registry on the workstation. There are instructions available for manually installing the addin, which if followed show up where the problems are. http://forums.nbn.org.uk/viewtopic.php?id=5249&p=2

Mike Weideli

MikeWeideli's Website

8 Reply by TonyP

  • TonyP
  • TonyP
  • Veteran
  • Offline
  • From: Somerset
  • Registered: 15-05-2006
  • Posts: 306

Re: NBN export leap year problem

Yes I too have been given a list of keys [really helpful there; no attributes] and am editing each of the records. Don't understand from the above where the problem starts but if the NBN keep being tetchy over it I have no other solution but to manually change all 390 occurrences that have been allowed to be incorrect.

Worth noting R6 does not care about leap years so 29th Feb is always considered valid!

Still wish it knew more about dates. Like date of death. Wish I could enter one. Can't as you have to know their date of birth and entering Unknown does not work for DOB if you put in DOD. So annoying when you contact a dead person their families are not impressed rather like me over this simple bug that should have NEVER have occurred.

Data Manger
Somerset Environmental Records Centre

TonyP's Website

9 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,645

Re: NBN export leap year problem

I remain to be convinced that there is anything wrong with the way R6 handles recording dates. Please can you be more specific  about where 29 Feb is accepted when it shouldn't be ?.  I agree that the validation of dates for the DOB/DOD is too restrictive. There is a plan to fix this once funding for 2015/2016 is agreed.

Mike Weideli

MikeWeideli's Website