Re: Translating vague dates into a single field?
Is their code around for translating the three columns of vague dates into the single column that is reported in Recorder?
How does it get done?
Ta
Tullie House Museum
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → How Do I... & Other Questions → Translating vague dates into a single field?
Is their code around for translating the three columns of vague dates into the single column that is reported in Recorder?
How does it get done?
Ta
Hi Matt
The conversion is done using code in the Recorder client application. However, there is some useful information on the Wiki page http://eim.metapath.org/wiki/index.php? … ague_Dates which might help.
Best Wishes
Not really, as we have 12 classes of date all interpreted from these two date fields.
Do we have to build a massive lookup system to interpret them - or export them and bind them back in as text?
Is there a more elegant solution, or is it as brutal as above.
M
Hi Matt
In order to give you further advice it might be handy to know exactly how you are planning to go about using the date strings? Is it for a report or similar?
Cheers
Mike Weideli has written a few functions for converting between dates. They might be installed already if you're on 6.13. Have a look at the functions that begin with LC. There is LCReturnDateShort and LCReturnDate which should do the job for you. LCToRataDie goes in the other direction - it converts a dd/mm/yyyy format date into the vague date format.
If you don't have them installed already, get in touch with Mike and he should be able to help.
Our Wildlife Trust is transferring their Recorder database to CMS and somewhere along the line need to translate all the vague dates out to a single field (as I currently understand it).
So, while reporting on the records does the job, its how that is implemented at the back that is of interest.
But Ill have a look at the functions and see what Mike has to offer.
M
I try to avoid the following solution because frankly it's very time consuming to write, but when I reach the point where lookup tables become too complex, I turn to a Visual Basic solution (usually within MAccess) where each line of data is examined one at a time.
The three fields will break down into their separate components (day, month, year) as best as can be managed then reassembled into CMS format.
Initially you would have lots of nested IF...THEN or SELECT...CASE..ELSE routines but as soon as something begins to take shape you can bung that bit of code into a Module and use the Call routines.
If you get a particularly effective piece of Module coding, it can be built directly into an MSAccess Query.
This is a rather ancient approach nowadays, I'm inclined to agree with Charles about Mike Weideli's more modern approaches, I've fiddled about with those functions and they do have a lot to offer.
Yeah, the actual code is just some rather complicated maths (to my eyes it's complex, but I'm not good at maths). Here:
USE [NBNData]
GO
/****** Object: UserDefinedFunction [dbo].[LCReturnDateShort] Script Date: 08/13/2008 16:14:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[LCReturnDateShort]
(@VagueDate int, @VagueDateType varchar(2), @DatePart varchar(1)) RETURNS nvarchar(10)
/* Returns a date or date part for a given vague date field
Author Michael Weideli - Littlefield Consultanct 14 October 2005
F RETURNS FULL dd/mm/yyyy
D RETURNS DAY
Y RETURNS YEAR
M RETURNS MONTH
vague date type of U does not return anything
EXAMPLE USAGE:
LCReturnDate(VAGUE_DATE_START, VAGUE_DATE_END, 'F')
*/
AS
BEGIN
declare @MM1 nvarchar(9)
declare @MM2 nvarchar(9)
declare @RP1 nvarchar(10)
declare @Z int
declare @G int
declare @H int
declare @A int
declare @B int
declare @Y int
declare @C int
declare @M int
declare @D int
declare @RD int
declare @Y4 int
declare @RD1 int
declare @RM1 int
declare @RY1 int
declare @RDS1 nvarchar(2)
declare @RDS2 nvarchar(2)
declare @RD2 int
declare @RM2 int
declare @RY2 int
declare @RMS1 nvarchar(2)
declare @RMS2 nvarchar(2)
set @RD = @VagueDate + 693594
set @Z = @RD + 306
set @H = 100 * @Z - 25
set @A = FLOOR(@H / 3652425)
set @B = @A - FLOOR(@A / 4)
set @Y = FLOOR((100 * @B + @H) / 36525)
set @C = @B + @Z - 365 * @Y - FLOOR(@Y / 4)
set @M = (5 * @C + 456) / 153
set @G = (153 * @M - 457) / 5
set @D = @C - @G
if @M >12 begin
set @Y = @Y+1
set @M = @M-12
end
set @RD1 = @D
set @RM1 = @M
set @RY1 = @Y
if @RD1 < 10 begin
set @RDS1 = '0' + str(@RD1,1,0)
end
else begin
set @RDS1 = str(@RD1,2,0)
end
if @RM1 < 10 begin
set @RMS1 = '0' + str(@RM1,1,0)
end
else begin
set @RMS1 = str(@RM1,2,0)
end
if @VagueDatetype <> 'U' begin
set @RP1 = case @Datepart
when 'D' then str(@RD1,2,0)
when 'M' then str(@RM1,2,0)
when 'Y' then str(@RY1,4,0)
when 'F' then @RDS1 + '/' + @RMS1 + '/' + str(@RY1,4,0)
end
end
return @RP1
RETURN ''
END
There was also some further information about vague dates on the wiki, but the wiki seems to have disappeared.
Forum → How Do I... & Other Questions → Translating vague dates into a single field?
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.