MySQL Forums
Forum List  »  MySQL Workbench

date difference
Posted by: Robert Lee
Date: April 14, 2019 12:30PM

Context: I am new to sql and reading 'SQL Queries for Mere Mortals 2nd Edition', chapter 5.

I am using a sample database provided by the textbook. I am having difficulty generating the correct value, number of years a staff has been with a school since a given date.

Q: How many years has each staff been with the school since 2007-10-01? (I am using the standard SQL year format, YYYY-MM-DD.)
Example: There is one staff member who has been with the school since 1982-11-20. So the correct answer to the question for this specific staff member should be roughly 25 years. '2007-10-01' - 1982-11-20 (column Date_Hired)

cast(cast('2007-10-01' - date_hired as unsigned) / 365 as unsigned ) as Years_with_School
from staff

However, this expression does not provide me with the correct answer. This expression above returned a value of 50539024859423925. I thought I needed to used the keyword integer, but MYSQL does not say is the right syntax. I found out that I had to use the keywords unsigned or signed to convert it to an integer.

Options: ReplyQuote

Written By
date difference
April 14, 2019 12:30PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.