* Sorting Before and After dates in Queries.

[E Wilcock]

I have World War 2 databases in which I use after and before death dates a lot.
I am having difficulty sorting these.
For people who died for any reason before 8 June 1945 but whose death date is unknown, I enter the date in fh as "before 8 June 1945".
"After May 1945" is correctly sorting above "after June 1945". However "after 1945" is sorting above the May and June 1945 known dates.
I apologise if you have already answered this Mike. I looked though my notes but when I tried to run the query which I thought you suggested, I got no dates in the death date column.
(I realise the war ended on May 7, not June 8.)
Or I could replace my after 1945 dates with after 1 Jan 1946?

[Mark1834]

I know I'm not Mike, but just in case he's not online today (I believe it does happen occasionally.. )...

The basic problem here is that FH does not implement date qualifiers correctly. Where a date element is missing, FH defaults it to the earliest value. So "after 1945" is interpreted by FH as "after 1 Jan 1945". This is contrary to both the GEDCOM specification and the normal natural language meaning of the phrase. A workaround in FH7 is to define a separate sort date. For example, leave "after 1945" as the entered date, but have the sort date as "after 1946". It's untidy, as it is introducing one error to correct another, but at least the displayed order will be correct.

[E Wilcock]

Thank you so much. I have not used sort dates in fh, but I came to fh from TMG where sort dates were very useful and I used them. And then used Mike's plug in to add sort dates to my personal trees. Just havent tried it on large place studies.

[Mark1834]

After I posted my first comment, I thought it worthwhile to benchmark FH against its competitors. FTM 2012 (the only version I have) has the same flaw, but RM7 sorts correctly, placing "after 1945" as later than any defined date in 1945.

[Mark1834]

I raised a ticket with CP regarding sort order. I had a detailed reply this evening that basically comes down to a deliberate policy that “after” should be interpreted as “during or after”, and “before” should be interpreted as “before or during”. It is therefore correct that “after 1945” is sorted as happening earlier than “May 1945”....

[LornaCraig]

Yes, that has always seemed a bit odd. In this case FH seems out of step with the common use of language.

On the other hand when it comes to "between" dates our everyday use of language is also a bit odd. Strictly speaking "between 1943 and 1945" should mean 1944. But it is usually intended to mean "between the beginning of 1943 and the end of 1945", which is how FH interprets it (helpfully, in this case).

[Mark1834]

Maybe it’s my inner cynicism surfacing again, but I suspect it might have its origins in slightly lazy coding. Treat all missing vales as 1, and ignore all qualifiers when sorting. After all these years, it becomes entrenched as the FH way of doing things, so unlikely to change.

[tatewise]

I suspect you are correct Mark.

It is theoretically possible to do better than FH when sorting Dates by using the XDATETYPE qualifier.
See the end of https://www.family-historian.co.uk/help ... rmats.html for details.
For example:
If XDATETYPE = After then =CalcDate(Date,+1) i.e. For After dates add 1 year
If XDATETYPE = Before then =CalcDate(Date,-1) i.e. For Before dates subtract 1 year

A full expression would be something like:
=DateIf( Text(Month(%FACT.DATE%)) = "",
DateIf( %FACT.DATE:XDATETYPE% = "After", CalcDate(%FACT.DATE%,1,0,0),
DateIf( %FACT.DATE:XDATETYPE% = "Before", CalcDate(%FACT.DATE%,-1,0,0),
%FACT.DATE%) ),
%FACT.DATE%)

It needs to test the Date Month is missing so that the 1 year offset is only applied to whole year dates.
I don't think similar code is needed for From and To dates.

[Mark1834]

Agree. Sorting say “before May 1945” as 1 May 1945, and “from/between 1945 and 1948” as 1 Jan 1945 is reasonable. It’s only where “after” is used with an incomplete date that additional processing is required to increment the least significant specified value and avoid sequencing that looks odd. So for example, “after May 1945” should sort as 1 Jun 1945 and “after 1945” should sort as 1 Jan 1946.

It is what RM appears to do, but FH have chosen otherwise.

[AdrianBruce]

... when it comes to "between" dates our everyday use of language is also a bit odd. Strictly speaking "between 1943 and 1945" should mean 1944. But it is usually intended to mean "between the beginning of 1943 and the end of 1945", which is how FH interprets it (helpfully, in this case).

I think you're right, Lorna. Natural language is flakey on this topic - if it weren't then mathematicians would never have needed to develop a phrase like "A is strictly greater than B" (which doesn't allow the equality of A and B).

If I saw the phrase "He died between 1939 and 1945", I would automatically allow for equality. If I saw "He died after 1939" - what would I do? Not sure... But of course, mathematically that's the same as the first half of "He died between 1939 and 1945"....

I have a distinct feeling that the safest way is to always allow for equality - which would mean that "greater than 1945" means "greater than or equal to 1 Jan 1945". That may be why FH does what it does - it's a defensive principle that actually might not make sense when applied in some aspects but probably does in others. The least worst choice, in other words. Maybe....