Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

Databases

Posted by Jeremy O'Bryan
Jeremy O'Bryan
16,672 Points

subtracting dates in mysql

Hi friends!

I'm having trouble with an issue in which I'm trying to obtain age information by subtracting birth / death dates in MySQL . I'm working with a database I created that contains information about U.S. Presidents and I'm trying to get it to show how old each one lived to be. The basic information looks like this:

+--------+------------+------------+------------+------------+
| number | first_name | last_name  | born       | died       |
+--------+------------+------------+------------+------------+
|      1 | George     | Washington | 02-22-1732 | 12-14-1799 |
|      2 | John       | Adams      | 10-30-1735 | 07-04-1826 |
|      3 | Thomas     | Jefferson  | 04-13-1743 | 07-04-1826 |

I'm attempting to use the DATEDIFF function in MySQL to get their ages but it doesn't seem to be working:

mysql> SELECT first_name , last_name , born, died, DATEDIFF(died,born) AS age FROM presidents WHERE died IS NOT NULL;
+------------+------------+------------+------------+------+
| first_name | last_name  | born       | died       | age  |
+------------+------------+------------+------------+------+
| George     | Washington | 02-22-1732 | 12-14-1799 | NULL |
| John       | Adams      | 10-30-1735 | 07-04-1826 | NULL |
| Thomas     | Jefferson  | 04-13-1743 | 07-04-1826 | NULL |

any help would be much appreciated :)

2 Answers

Jennifer Nordell
seal-mask
STAFF
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher
Jennifer Nordell
.a{fill-rule:evenodd;}techdegree
Jennifer Nordell
Treehouse Teacher

Hi there, Jeremy O'Bryan! I'm a little curious about the date you have stored there. In every example I can find of the DATEDIFF in MySQL, it lists the date in the format 2020-10-06. I'm curious if they are being stored as dates or strings, and also if it affects anything if you change the dates to be in the format year-month-day.

Hope this helps! :sparkles:

Jeremy O'Bryan
Jeremy O'Bryan
16,672 Points

Thanks! Yep, that did it, completely overlooked the fact that I was trying to work with a string!