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 ... |
Get Oracle PL/SQL Programming: A Developer's Workbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.