Oracle PL/SQL Tutorial/PL SQL Data Types/Date Functions

ADD_MONTHS

ADD_MONTHS function adds a number of months to the specified date:

   <source lang="sql">

v_dt:= ADD_MONTHS(date,integer);</source>

LAST_DAY

The LAST_DAY function retrieves the last day of the month in the specified date.

   <source lang="sql">

v_dt:= LAST_DAY (date);</source>

MONTHS_BETWEEN

The MONTHS_BETWEEN function is shown here:

   <source lang="sql">

v_nr:= MONTHS_BETWEEN(date1,date2);</source>

Returning a point just before a specific midnight with TRUNC

   <source lang="sql">

SQL> SQL> create or replace function f_getMidnight_dt (i_date_dt DATE) return date is

 2  begin
 3      return trunc(i_date_dt)+1-1/(24*60*60);
 4  end;
 5  /

Function created. SQL> SQL> select f_getMidnight_dt(sysdate) from dual; F_GETMIDN

---

10-JUN-07</source>

ROUND

ROUND, typically applied to numbers, also work with dates and timestamps.

ROUND rounds the date to a specified point.

   <source lang="sql">

v_dt:= ROUND (DATE|TIMESTAMP|INTERVAL value[,PRECISION]);</source>

TO_DATE("29-DEC-1988","dd-mon-yyyy")

   <source lang="sql">

SQL> SQL> DECLARE

 2     start_date DATE;
 3     end_date DATE;
 4     service_interval INTERVAL YEAR TO MONTH;
 5     years_of_service NUMBER;
 6     months_of_service NUMBER;
 7  BEGIN
 8
 9     start_date := TO_DATE("29-DEC-1988","dd-mon-yyyy");
10     end_date := TO_DATE ("26-DEC-1995","dd-mon-yyyy");
11
12  END;
13  /

PL/SQL procedure successfully completed. SQL></source>

TRUNC and ROUND

TRUNC (value,"W") is the easiest way of getting the first day of the week.

TRUNC, typically applied to numbers, also work with dates and timestamps.

TRUNC truncates the date to some level of precision.

   <source lang="sql">

v_dt:= TRUNC (DATE|TIMESTAMP|INTERVAL value[,PRECISION]);</source>

Use TRUNC to get only the time

   <source lang="sql">

SQL> create or replace function f_getTime_tx (i_diff_nr NUMBER)return VARCHAR2 is

 2      v_out_tx VARCHAR2(2000);
 3
 4      v_hr_nr NUMBER;
 5      v_min_nr NUMBER;
 6      v_sec_nr NUMBER;
 7
 8  begin
 9      v_hr_nr:=trunc(i_diff_nr/(60*60));
10      v_min_nr:= trunc((i_diff_nr-v_hr_nr*60*60)/60);
11      v_sec_nr:=mod(i_diff_nr,60);
12
13      v_out_tx:=v_hr_nr||" hours "||
14                 v_min_nr||" min "||v_sec_nr||" sec";
15      return v_out_tx;
16  end f_getTime_tx;
17  /

Function created. SQL> SQL> select f_getTime_tx(123) from dual; F_GETTIME_TX(123)

---

0 hours 2 min 3 sec SQL></source>

Working with dates and built-in functions EXTRACT

EXTRACT function takes a single part of a date/interval/timestamp (year only, month only, and so on, up to the second).

   <source lang="sql">

v_nr:= EXTRACT (TYPE from DATE|TIMESTAMP|INTERVAL value);</source>