Marla Posted January 16, 2018 Report Share Posted January 16, 2018 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. Quote Link to comment Share on other sites More sharing options...
Merv Posted January 16, 2018 Report Share Posted January 16, 2018 Hi Marla, Posting the code that generates the report would be helpful. Quote Link to comment Share on other sites More sharing options...
Marla Posted January 16, 2018 Author Report Share Posted January 16, 2018 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 Quote Link to comment Share on other sites More sharing options...
Marla Posted January 16, 2018 Author Report Share Posted January 16, 2018 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. Quote Link to comment Share on other sites More sharing options...
Merv Posted January 17, 2018 Report Share Posted January 17, 2018 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 Quote Link to comment Share on other sites More sharing options...
Marla Posted January 17, 2018 Author Report Share Posted January 17, 2018 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 50You 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 Quote Link to comment Share on other sites More sharing options...
Marla Posted January 17, 2018 Author Report Share Posted January 17, 2018 Who can I contact TNG Support? Darrin? Or Simplyhosting? Quote Link to comment Share on other sites More sharing options...
stores Posted January 17, 2018 Report Share Posted January 17, 2018 For what it is worth Marla, I tested Merv's code and got the following results... See Attached Quote Link to comment Share on other sites More sharing options...
Marla Posted January 17, 2018 Author Report Share Posted January 17, 2018 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? Quote Link to comment Share on other sites More sharing options...
stores Posted January 17, 2018 Report Share Posted January 17, 2018 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... Quote Link to comment Share on other sites More sharing options...
Merv Posted January 17, 2018 Report Share Posted January 17, 2018 I should have added a screen shot earlier... Quote Link to comment Share on other sites More sharing options...
Marla Posted January 17, 2018 Author Report Share Posted January 17, 2018 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. Quote Link to comment Share on other sites More sharing options...
Merv Posted January 17, 2018 Report Share Posted January 17, 2018 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. Quote Link to comment Share on other sites More sharing options...
jayat1familytree Posted January 17, 2018 Report Share Posted January 17, 2018 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 Quote Link to comment Share on other sites More sharing options...
Marla Posted January 17, 2018 Author Report Share Posted January 17, 2018 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! Quote Link to comment Share on other sites More sharing options...
bhemph Posted January 18, 2018 Report Share Posted January 18, 2018 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 Quote Link to comment Share on other sites More sharing options...
Kare Posted January 18, 2018 Report Share Posted January 18, 2018 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. Quote Link to comment Share on other sites More sharing options...
jayat1familytree Posted January 18, 2018 Report Share Posted January 18, 2018 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 Quote Link to comment Share on other sites More sharing options...
Marla Posted January 18, 2018 Author Report Share Posted January 18, 2018 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. Quote Link to comment Share on other sites More sharing options...
Marla Posted January 18, 2018 Author Report Share Posted January 18, 2018 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 Quote Link to comment Share on other sites More sharing options...
Kare Posted January 18, 2018 Report Share Posted January 18, 2018 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... Quote Link to comment Share on other sites More sharing options...
Merv Posted January 19, 2018 Report Share Posted January 19, 2018 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. Quote Link to comment Share on other sites More sharing options...
JWidner Posted August 27, 2018 Report Share Posted August 27, 2018 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: Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.