title: Oracle SQL Query involving Rounded Month conversion of time, and date interval calculation
body: <p>I am working on a homework problem. For Database SQL programming.</p>
  
  <p>Q4. Using today’s date (current date), determine the age (in months) of each book that the bookstore sells. Make sure whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age. Use column alias for the age column.  </p>
  
  <p>Query Results Desired:</p>
  
  <pre><code>TITLE                           PUBDATE                   SYSDATE            AGE                    
  BODYBUILD IN 10 MINUTES A DAY  | 21-JAN-01 00:00:00 | 14-NOV-12 13:16:35 | 141  
  REVENGE OF MICKEY              | 14-DEC-01 00:00:00 | 14-NOV-12 13:16:35 | 131  
  BUILDING A CAR WITH TOOTHPICKS | 18-MAR-02 00:00:00 | 14-NOV-12 13:16:35 | 127    
  DATABASE IMPLEMENTATION        | 04-JUN-99 00:00:00 | 14-NOV-12 13:16:35 | 161 
  COOKING WITH MUSHROOMS         | 28-FEB-00 00:00:00 | 14-NOV-12 13:16:35 | 152 
  HOLY GRAIL OF ORACLE           | 31-DEC-01 00:00:00 | 14-NOV-12 13:16:35 | 130 
  
  14 rows selected 
  </code></pre>
  
  <p>It's a table for books and has pub_date, and I'm using sysdate for some interval calculations.  The data type for pub_date is DATE.  </p>
  
  <p>I've worked out the problem, but I'm having trouble getting this rounded time for the pubdate.  It's supposed to be rounded with HH:MM:SS as 00:00:00, but my coding isn't getting that correct.  Here is what I've done:</p>
  
  <pre><code>SELECT title, TO_CHAR(ROUND(pubdate), 'DD-MON-YY HH24:MM:SS') AS pubdate,
    TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MM:SS') AS "SYSDATE", 
    ROUND(SYSDATE - TO_DATE(pubdate)) AS age
  FROM books;
  </code></pre>
  
  <p>Something is wroung with my calculation of the age, <code>ROUND(SYSDATE - TO_DATE(pubdate))</code>.</p>
  
  <p>And something is wrong with my coding of the rounded pubdate, <code>TO_CHAR(ROUND(pubdate), 'DD-MON-YY HH24:MM:SS') AS pubdate</code>.  </p>
  
  <p>Just wondering how to fix my mistakes on the calculation of age and how to get the correct rounded date like it has it there.</p>
  
  <p>This question refers to Murach's Oracle SQL, Ch17 How to work with timestamps and intervals.  </p>