Wrong Years in SQL

Question

I have been trying to work this problem out for hours. My numbers are about a year off. Can someone please tell me what I am doing wrong?

Here is the question and expected results: enter image description here Create a list of student names from area code 203 along with the number of years since they registered (show 2 decimal places on all values). Sort the list on the number of years from highest to lowest.

Here is my code and results:

SELECT First_Name,
       Last_Name,
       TO_CHAR((ROUND(SYSDATE - registration_date) / 365), '9.99') AS YEARS
FROM Student
WHERE Phone LIKE '203-%%%-%%%%' 
ORDER BY  Years DESC  ;

enter image description here

FYI: `%` matches zero or more characters, `_` matches exactly one character. — Shannon Severance, Sep 30 '14 at 23:06
Looks like it works fine to me. [SQLFiddle here](http://sqlfiddle.com/#!4/33f2f/5). Share and enjoy. — Bob Jarvis - Слава Україні, Oct 01 '14 at 03:12
Is the data you're using, including the registration dates, fixed in the assignment? If so, maybe the example output was just created a bit more than a year ago? Since the query is based on the current date, you'd expect the output to be different depending on when the query is executed, surely? — Alex Poole, Oct 01 '14 at 07:33
You should post questions as (plain) text. Posting a screenshot of your PDF reader is not helpful. The example output is also better posted as formatted plain text. — , Oct 01 '14 at 09:42
Another thing: Just dividing the number of days with 365 will give results that are slightly too small if there are leap years between registration date and sysdate. Might be too small an error to catch with two decimals, but with many leap years it just might be noticable. In that case you could consider something like `MONTHS_BETWEEN(SYSDATE,REGISTRATION_DATE) / 12` or `MONTHS_BETWEEN(TRUNC(SYSDATE),REGISTRATION_DATE) / 12`. — Kim Berg Hansen, Oct 01 '14 at 12:42

Alex Poole · Answer 1 · 2014-10-01T09:34:33.637

Your query is based on the current date, so with fixed values for the data you've been given, you would expect it to differ from the sample output in the question. That output is based on whenever the question was set and the query was run - whatever the date was that day. (Apparently 2013-06-03, using an adjusted subset of the schema build found here).

So on that date, a registration date of, say, 2007-02-02 was 6.34 years ago, but it's now 1.33 years later, so the same query gets an answer of 7.67 today.

You're assuming the results have to match exactly what you've been given, when it's really only showing you the format it should be in. The exact number of years has to be expected to be different. So your query isn't wrong, you're misinterpreting the question slightly.

Also ROUND will give you slightly different results if the query is run in the morning or afternoon. It's more normal to use TRUNC(sysdate) to ignore the time portion. And as pointed put in comments, your wildcard usage is wrong, and you could just use LIKE '203-%'.

Wrong Years in SQL

1 Answers1