Timestamps and Intervals: NUMTOYMINTERVAL SYSTDATE CALCULATION SQL QUERY

Posted by MeachamRob on Stack Overflow See other posts from Stack Overflow or by MeachamRob
Published on 2012-12-03T10:39:10Z Indexed on 2012/12/03 11:12 UTC
Read the original article Hit count: 150

Filed under:
|

I am working on a homework problem, I'm close but need some help with a data conversion I think. Or sysdate - start_date calculation

The question is:

Using the EX schema, write a SELECT statement that retrieves the date_id and start_date from the Date_Sample table (format below), followed by a column named Years_and_Months_Since_Start that uses an interval function to retrieve the number of years and months that have elapsed between the start_date and the sysdate. (Your values will vary based on the date you do this lab.) Display only the records with start dates having the month and day equal to Feb 28 (of any year).

DATE_ID     START_DATE                          YEARS_AND_MONTHS_SINCE_START 
2           Sunday   , February  28, 1999       13-8                         
4           Monday  , February  28, 2005        7-8                          
5           Tuesday  , February  28, 2006       6-8 

Our EX schema that refers to this question is simply a Date_Sample Table with two columns:

DATE_ID     NUMBER NOT Null

START_DATE  DATE

I Have written this code:

SELECT date_id, TO_CHAR(start_date, 'Day, MONTH DD, YYYY') AS start_date , 
  NUMTOYMINTERVAL((SYSDATE - start_date), 'YEAR') AS years_and_months_since_start
FROM  date_sample
WHERE TO_CHAR(start_date, 'MM/DD') = '02/28';

But my Years and months since start column is not working properly. It's getting very high numbers for years and months when the date calculated is from 1999-ish. ie, it should be 13-8 and I'm getting 5027-2 so I know it's not correct. I used NUMTOYMINTERVAL, which should be correct, but don't think the sysdate-start_date is working. Data Type for start_date is simply date. I tried ROUND but maybe need some help to get it right.

Something is wrong with my calculation and trying to figure out how to get the correct interval there. Not sure if I have provided enough information to everyone but I will let you know if I figure it out before you do.

It's a question from Murach's Oracle and SQL/PL book, chapter 17 if anyone else is trying to learn that chapter. Page 559.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about query