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