* Date format in queries

[MLawrence]

Sorry if this has already been addressed, have been unable to find an answer.

How do I get a query to provide the date, such as birth date, with leading zeros?
I use %INDI.BIRT[1].DATE:ABBREV3% but instead of 01/01/1867, for example, it returns
1/1/1867.

Thanks for any suggestions
Kind regards
Margaret

[tatewise]

It is not at all easy. There are no built-in functions to do that.
Can you explain why it is important?

It would need a complex expression that extracted the day number, tested for less than 10, and added a leading zero.
The same would be needed for the month number. Then all the components would need joining together.

[MLawrence]

Thanks for the response.

I am exporting the data from charts to excel (eventually) and using that data to print labels for the family group trees I'm working on.
01/01/1867 - 05/09/1900 looks much tidier than 1/1/1867 - 5/9/1900 especially when the marriage date actually does have the leading zeroes (bit inconsistent).

It's not a big deal as I can manipulate these dates in excel, just thought if there was a neat way to do this in the query then I'd do it there.

[tatewise]

This Expression is getting there:
=Text(TextIf(%FACT.DATE:DAY% <= 9,Text("0" . Text(%FACT.DATE:DAY%)),Text(%FACT.DATE:DAY%)) . "/" . TextIf(%FACT.DATE:MONTH_NUMBER% <= 9,Text("0" . Text(%FACT.DATE:MONTH_NUMBER%)),Text(%FACT.DATE:MONTH_NUMBER%)) . "/" . Text(%FACT.DATE:YEAR%))

But it produces 00 when there is no Day or no Month defined.
It also does not handle Quarter Dates, Date Ranges, Date Periods or missing dates. Have you considered those?

BTW: Do you know that a spreadsheet like Excel does NOT handle dates prior to 1 Jan 1900?
So you will have to make sure they are not using date formats in the Query Result Set.
I strongly advise you to check the whole process through with pre-1900 and post-1900 dates using Excel.
I suspect it might be possible to produce the labels directly in FH.

How are you producing the family group trees. Are you using FH Diagram charts?
If so then they can have 'labels' included as a descriptive title box and printed with the Diagram.

[jimlad68]

For excel I always use yyyymmdd or similar 1880-02-01, that at least sorts " out of the box", but of course does not work with date functions.

[MLawrence]

Hi Mike

Thanks for the response and advice. As you point out quarter dates are a consideration, I tend to enter them in FH as Qn YYYY, where n is the number 1 to 4.

I am running a custom descendants query which I then export to csv.
The query can be modified for start person and the number of generations required. I export Descendant, birth date & place, Death date & place, spouse name, spouse birth date & place, spouse marriage date & place, spouse death date & place (plus spouse2, spouse3, spouse4 - which meet my needs).

When I import the csv into excel, during the import, at the data preview step, I set each date field to text so that it doesn't try to convert the date to a number (which is what excel uses for dates) or to default to date format - that way it ensures that the dates before 1900 are ok.

I then do a mailmerge in Word using a label template. Works pretty well.

Kind regards
Margaret

[tatewise]

Margaret, with so many dates from so many facts it is likely that most date formats will crop up.
Thus it will have to cope with Quarter dates, date Ranges, dates with day or month missing, etc, etc...
So you will just have to accept what FH produces I'm afraid.

[MLawrence]

Thanks Mike, I appreciate your help and advice.

I have a work around so it's not a major issue.

Kind regards
Margaret