1

Topic: Update taxon occurrence dates

Hi

I have a survey containing over 2,000 taxon occurrences spread across 467 survey events and samples (one sample per survey event).  Currently all the occurrences have dates with the range '1980 - 1989' but we now have more precise dates (e.g. '02/09/1986' or 'August 1984') for every record spread across this range (with the TOCC keys).  Does anyone have a batch update that will allow me to update the sample/survey event dates for these records.

Or can anyone suggest how I might be able to update the dates for these records?  Would it be possible to re-import these records (without deleting them first so that any other changes in R6 aren't lost) using just the TOCC key and date to update them?

I can't even find a SQL Server user-function to convert text dates into the R6 vague date components so that I can try doing it in native SQL.

Any ideas would be appreciated.

Many thanks
Andy

Andy Foy
Systems Manager
Greenspace Information for Greater London (GiGL) CIC
www.gigl.org.uk

2

Re: Update taxon occurrence dates

Hi

I think it would be safer to use a batch update. Trying to reimport may cause unwanted effects on changes  made to the data.  The difficulty is that  the batch update can't access the code which works out the R6 vague date start, end and type.  The closest I have is a udf [dbo].[LCReturnDateType] which takes the vague start, vague date end and type of DD to work out the correct vague date type. To use this you would need to put the Taxon Occurrence key, the vague date start, vague date end in a csv file . This means converting  say Aug 2000 to a vague date start of 1/8/2000 and a vague date end of 31/8/2000, or 2000 to vds 1/1/2000 and vde of 31/12/2000. Exact dates just need to same VDS and VDE.

You could then use this in a Batch update which picks up the csv file to change the Survey Event/Sample and Taxon Determination dates to the dates you require, using the two udf's  eg.

new vds as integer = (dbo.LCToRataDie(vds) where vds is the start field in your csv file eg 1/8/2000) 
new vde as integer = (dbo.LCToRataDie(vde) where vde is the end date field in your csv file  eg 31/8/2000)

new vdt as varchar (2) =  [dbo].[LCReturnDateType](dbo.LCToRataDie(vds),  dbo.LCToRataDie(vde),'DD'))

New UDF is below. I have used this on a special import following the  method outlined above. 




Mike
 
   


USE [NBNData]
GO
/****** Object:  UserDefinedFunction [dbo].[LCReturnDateType]    Script Date: 09/17/2017 11:56:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Michael Weideli
-- Create date: March 2017
-- Description:    Take a vague date start and vague date end and return
-- a vague date type for a DD vague date

-- =============================================
ALTER FUNCTION [dbo].[LCReturnDateType]
(@VagueDateStart int, @VagueDateEnd int, @VagueDateType varchar(2))
RETURNS varchar(2)
AS
BEGIN
declare @StartDay integer
declare @StartMonth integer
declare @StartYear integer
declare @StartMonthEnd integer
declare @EndDay integer
declare @EndMonth integer
declare @EndYear integer
declare @DayMonthEnd integer
declare @FullDateStart varchar(10)
declare @FullDateEnd varchar(10)
declare @NuVagueDateType varchar(2)


if @VagueDateType <> 'DD' begin
  Set @NuVagueDateType = @VagueDateType
end else begin   
  SET @NuVagueDateType = 'DD'
  if @VagueDateStart = @VagueDateEnd begin
    set @NuVagueDateType = 'D'
  end else begin
    Set @FullDateStart = dbo.LCReturnDate(@VagueDateStart,@VagueDateStart,'F')     
    Set @FullDateEnd = dbo.LCReturnDate(@VagueDateEnd,@VagueDateEnd,'F')     
     
    Set @StartDay = cast(left(@FullDateStart,2) as int) 
    Set @EndDay = cast(left(@FullDateEnd,2) as int)
    Set @StartMonth = cast(substring(@FullDateStart,4,2) as int)
    Set @EndMonth = cast(substring(@FullDateEnd,4,2) as int)
    Set @StartYear = cast(substring(@FullDateStart,7,4) as int)
    Set @EndYear = cast(substring(@FullDateEnd,7,4) as int)
    Set @DayMonthEnd = [dbo].[LCReturnMonthEndDay](@EndMonth,@EndYear)

    If @StartDay = 1 and @StartMonth = 1 and @EndDay = 31 and
      @EndMonth = 12  begin
      if @StartYear = @EndYear begin
        Set @NuVagueDateType = 'Y'
      end else begin
        Set @NuVagueDateType = 'YY'
      end 
    end else
      if @StartYear = @EndYear and  @StartDay = 1 and @EndDay = @DayMonthEnd Begin
        if @StartMonth = @EndMonth begin
          Set @NuVagueDateType = 'O'
        end else begin
          Set @NuVagueDateType = 'M'   
        end
      end   
     


  end 
end
Return  @NuVagueDateType

END

Mike Weideli