Jump to content
TNG Community

From Number Birth/Death Years to Word Birth/Death Years


Marla

Recommended Posts

How can i change the numerical birth/death years to "word" birth/death years, such as 1 Jan 2018?  See Death and Birth Columns. Thank you in advance.image.png

Link to comment
Share on other sites

SQL: SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death, deathdatetr AS Death,birthdatetr AS Birth,CONCAT('', lastname,', ', firstname, '') as Name FROM tng_people WHERE living = 0 AND YEAR( birthdatetr ) !=0 and YEAR (deathdatetr) !=0 ORDER BY Age_at_Death DESC, Death, Birth, lastname ASC

Link to comment
Share on other sites

Please ignore the first SQL above.  

SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death, deathdatetr AS Death,birthdatetr AS Birth,CONCAT('<a href="getperson.php?personID=',personid,'&tree=', gedcom,'">', lastname,', ', firstname, '</a>') as Name FROM tng_people WHERE living = 0 AND YEAR( birthdatetr ) !=0 and YEAR (deathdatetr) !=0 ORDER BY Age_at_Death DESC, Death, Birth, lastname ASC 

 

That is the correct SQL.

 

Link to comment
Share on other sites

Hi Marla, Try this...

SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death,birthdate,deathdate,CONCAT('', lastname,', ', firstname, '') as Name FROM tng_people WHERE living = 0 AND birthdatetr !=0 and deathdatetr !=0 ORDER BY Age_at_Death DESC, deathdate, birthdate, lastname ASC

Link to comment
Share on other sites

Thank you Merv, but it is not working at all.  See the results below from my TNG.

An error has occurred in the TNG software. This could be due to a setup issue, an incomplete upgrade or a program bug. If you are the site owner, you may contact TNG support for help with this problem. Please copy the query below and paste it into your message.

Query: SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death,birthdate,deathdate,CONCAT(\'\', lastname,\', \', firstname, \'\') as Name FROM tng_people WHERE living = 0 AND birthdatetr !=0 and deathdatetr !=0 ORDER BY Age_at_Death DESC, deathdate, birthdate, lastname ASC LIMIT 50

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\'\', lastname,\', \', firstname, \'\') as Name FROM tng_people WHERE living = 0' at line 1

Link to comment
Share on other sites

For what it is worth Marla, I tested Merv's code and got the following results... See Attached

2018-01-17_093240.jpg

Link to comment
Share on other sites

Thank you Stores but your SQL showing is incomplete and I cant see more after "CO... in the first line...  Can you please copy your full SQL and paste it in posting for me? 

 

 

Link to comment
Share on other sites

The code is exactly as Merve posed it, right up to the ASC, I simply copied his posted code to a new report and ran it...

Link to comment
Share on other sites

Thank you, Merv.  the same code from you I pasted into my TNG that It is not working at all.  Still having ERROR.  I still don't understand why I am ALWAYS having this problematic when I create any reports.  Apparently there is some bug or something wrong with my TNG.  I had been contacted Simplyhosting and I was told by Simplyhosting to contact Darrin. I already sent an email to him. Waiting to hear from Darrin.  Thank you again.  

Link to comment
Share on other sites

36 minutes ago, Marla said:

Still having ERROR.  I still don't understand why I am ALWAYS having this problematic when I create any reports.

That has happened to me on a couple of occasions. It will be interesting to see what Darrin says.

Link to comment
Share on other sites

jayat1familytree

Marla,  Try it without the Concat 

SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death,birthdate,deathdate, lastname, firstname FROM tng_people WHERE living = 0 AND birthdatetr !=0 and deathdatetr !=0 ORDER BY Age_at_Death DESC, deathdate, birthdate, lastname ASC 

Link to comment
Share on other sites

1 hour ago, jayat1familytree said:

Marla,  Try it without the Concat 

SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death,birthdate,deathdate, lastname, firstname FROM tng_people WHERE living = 0 AND birthdatetr !=0 and deathdatetr !=0 ORDER BY Age_at_Death DESC, deathdate, birthdate, lastname ASC 

Hurrah!!  it works!!!    Thank you so much Jay!

Link to comment
Share on other sites

Marla,
     It looks like when you did the CONCAT part that Merv posted, you have single quotes that are escaped with the backslash each time.  So you should be able to replace that CONCAT with the code from your original CONCAT to get the clickable link that you had in your original query for the name.  I thought I remembered seeing something about the escape characters being added incorrectly to reports somewhere, but I can't seem to find it so maybe my mind made up that memory.  You could always go into the cpanel and edit the sqlselect text field in the reports table directly if the escape characters end up showing up in your query when they shouldn't be there.

SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death, deathdate AS Death,birthdate AS Birth,CONCAT('<a href="getperson.php?personID=',personid,'&tree=', gedcom,'">', lastname,', ', firstname, '</a>') as Name FROM tng_people WHERE living = 0 AND YEAR( birthdatetr ) !=0 and YEAR (deathdatetr) !=0 ORDER BY Age_at_Death DESC, deathdatetr, birthdatetr, lastname ASC

That query above is your original with just the four minor changes needed to get the Death and Birth columns to say the date that you entered into the birth and death date fields.

Brent

Link to comment
Share on other sites

There is a 'problem' with the above code if the birth and/or death field does not include both date, month and year. Filling in only year, only month and year or say abt 1900 for a person, results in a 'blank' age_at_death.

Link to comment
Share on other sites

jayat1familytree

You cannot 'calculate' someone's age if you don not have a complete month day and year.

Just like as stated on the statistics page;

  Age-related calculations are based on individuals with recorded birth and death dates. Due to the existence of incomplete date fields(e.g., a death date listed only as "1945" or "BEF 1860"), these calculations cannot be 100% accurate
Link to comment
Share on other sites

On 1/17/2018 at 12:42 PM, Merv said:

That has happened to me on a couple of occasions. It will be interesting to see what Darrin says.

Jay gave me his instruction to remove this part and it works for me.  Thank you, Merv.

Link to comment
Share on other sites

7 hours ago, bhemph said:

Marla,
     It looks like when you did the CONCAT part that Merv posted, you have single quotes that are escaped with the backslash each time.  So you should be able to replace that CONCAT with the code from your original CONCAT to get the clickable link that you had in your original query for the name.  I thought I remembered seeing something about the escape characters being added incorrectly to reports somewhere, but I can't seem to find it so maybe my mind made up that memory.  You could always go into the cpanel and edit the sqlselect text field in the reports table directly if the escape characters end up showing up in your query when they shouldn't be there.

SELECT FLOOR(DATEDIFF(deathdatetr, birthdatetr)/365.25) as Age_at_Death, deathdate AS Death,birthdate AS Birth,CONCAT('<a href="getperson.php?personID=',personid,'&tree=', gedcom,'">', lastname,', ', firstname, '</a>') as Name FROM tng_people WHERE living = 0 AND YEAR( birthdatetr ) !=0 and YEAR (deathdatetr) !=0 ORDER BY Age_at_Death DESC, deathdatetr, birthdatetr, lastname ASC

That query above is your original with just the four minor changes needed to get the Death and Birth columns to say the date that you entered into the birth and death date fields.

Brent

Brent, Thank you for your good advice and i will keep it in my file.  Marla

Link to comment
Share on other sites

3 timer siden, jayat1familytree skrev:

You cannot 'calculate' someone's age if you don not have a complete month day and year.

Just like as stated on the statistics page;

  Age-related calculations are based on individuals with recorded birth and death dates. Due to the existence of incomplete date fields(e.g., a death date listed only as "1945" or "BEF 1860"), these calculations cannot be 100% accurate

Well, that makes the report more or less useless for me. For 25% of the dead people in my database, most of them born before 1800, I don't have info about both date, month and year.... To say that "calculations cannot be 100% accurate" is certainly an understatement...

Link to comment
Share on other sites

2 hours ago, Marla said:

Brent, Thank you for your good advice and i will keep it in my file.  Marla

Marla, Brent's code is probably the better code to use as far as functionality is concerned as the names are clickable links. I didn't check for this functionality on my original code.

Link to comment
Share on other sites

  • 7 months later...

Not sure what you actually want, but if it is to simply get an output to show a date as something like 26 Aug 2018, then you can simply use the DATE_FORMAT command. I replied to a different post you made above using that method. Hopefully, that is what you are looking for.

See: 

 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...