Jump to content
TNG Community
Marla

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

Recommended Posts

Marla

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

Share this post


Link to post
Share on other sites
Merv

Hi Marla,

Posting the code that generates the report would be helpful.

Share this post


Link to post
Share on other sites
Marla

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

Share this post


Link to post
Share on other sites
Marla

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.

 

Share this post


Link to post
Share on other sites
Merv

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

Share this post


Link to post
Share on other sites
Marla

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

Share this post


Link to post
Share on other sites
Marla

Who can I contact TNG Support?  Darrin? Or Simplyhosting?

 

Share this post


Link to post
Share on other sites
stores

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

2018-01-17_093240.jpg

Share this post


Link to post
Share on other sites
Marla

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? 

 

 

Share this post


Link to post
Share on other sites
stores

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...

Share this post


Link to post
Share on other sites
Merv

I should have added a screen shot earlier...

Capture.JPG

 

Share this post


Link to post
Share on other sites
Marla

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.  

Share this post


Link to post
Share on other sites
Merv
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.

Share this post


Link to post
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 

Share this post


Link to post
Share on other sites
Marla
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!

Share this post


Link to post
Share on other sites
bhemph

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

Share this post


Link to post
Share on other sites
Kare

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.

Share this post


Link to post
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

Share this post


Link to post
Share on other sites
Marla
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.

Share this post


Link to post
Share on other sites
Marla
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

Share this post


Link to post
Share on other sites
Kare
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...

Share this post


Link to post
Share on other sites
Merv
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.

Share this post


Link to post
Share on other sites
JWidner

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: 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×