CODEWITHSUNDEEP
sqloracle-databasetimestampintervalsrounding
MeachamRob
MeachamRob

Reputation: 363

Oracle SQL Query involving Rounded Month conversion of time, and date interval calculation

I am working on a homework problem. For Database SQL programming.

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.

Query Results Desired:

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

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.

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:

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;

Something is wroung with my calculation of the age, ROUND(SYSDATE - TO_DATE(pubdate)).

And something is wrong with my coding of the rounded pubdate, TO_CHAR(ROUND(pubdate), 'DD-MON-YY HH24:MM:SS') AS pubdate.

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.

This question refers to Murach's Oracle SQL, Ch17 How to work with timestamps and intervals.

Upvotes: 0

Views: 1382

Answers (1)

Justin Cave
Justin Cave

Reputation: 231861

You can get the number of months between two dates using the months_between function. You can then truncate that to the nearest whole month.

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", 
       trunc( months_between( sysdate, pubdate )) AS age 
  FROM books;

Upvotes: 2

Related Questions