Oracle PL/SQL Programming: A Developer's Workbook

Chapter 19. Date Functions

Beginner

Q:
19-1.	Use the ADD_MONTHS function, as shown here: date_plus_6 := ADD_MONTHS (my_date, 6);
Q:
19-2.	You want to use the SYSDATE and TO_CHAR functions, along with the appropriate format mask: CREATE OR REPLACE PROCEDURE show_now IS BEGIN DBMS_OUTPUT.PUT_LINE (TO_CHAR (SYSDATE, 'Month DDth, YYYY HH24:MI:SS')); END; /
Q:
19-3.	The LAST_DAY function should do the trick: SQL> EXEC DBMS_OUTPUT.PUT_LINE (LAST_DAY(SYSDATE)) 31-MAR-99
Q:
19-4.	If you don’t want the time component possibly messing up your date-range computations, you can truncate the date as follows: my_date := TRUNC (my_date); The TRUNC function by default truncates the time component. You can use other format masks to specify different levels of truncation (day, month, week, year, etc.).
Q:
19-5.	Again, TRUNC is the way to go: my_date := TRUNC (my_date, 'Q');
Q:
19-6.	The MONTHS_BETWEEN function does most of the work, but you also have to truncate the fractional component: month_count := TRUNC (MONTHS_BETWEEN (start_date, end_date));
Q:
19-7.	There is no ADD_YEARS function, so you have to do a bit of translation. Here is the code to write in a database trigger that makes sure your employees are at least 18 years old: IF ADD_MONTHS (:NEW.hire_date, -1*18*12) < :NEW.date_of_birth THEN DBMS_OUTPUT.PUT_LINE ( 'Too young to hire...in the US anyway.'); END IF;
Q:
19-8.	The default time in Oracle is midnight: 12:00:00 A.M.
Q:
19-9.	The easiest way to do this is to use the LAST_DAY function: CREATE OR REPLACE FUNCTION days_left ...