11.17. Date Functions

11.17.1. SYSDATE

FUNCTION SYSDATE RETURN DATE;

This function returns the current date and time. It can be used in any PL/SQL expressions including initialization of variables. SYSDATE evaluates to a DATE type. If you assign SYSDATE to a string, Oracle will do an implicit conversion.

DECLARE
    today DATE := SYSDATE;
BEGIN
    NULL;
END;

You can set a default display format for your session with the ALTER SESSION statement.

SQL> ALTER SESSION SET
     NLS_DATE_FORMAT='dd-mon-yyyy hh24:mi:ss';

The session default display now includes date and time.

SQL> select sysdate from dual;

SYSDATE
--------------------
06-may-2004 12:47:07

The TRUNC function truncates a date to zero hours, minutes, and seconds. This is the earliest time possible for that day.

SQL> select TRUNC(SYSDATE) from dual;

TRUNC(SYSDATE)
--------------------
06-may-2004 00:00:00

To see if a date variable precedes the current date, compare it to the truncation of the current day.

IF date_variable < TRUNC(SYSDATE) THEN

11.17.2. TO_CHAR, TO_DATE

FUNCTION TO_CHAR(D DATE [,format_model VARCHAR]) RETURN VARCHAR2;

This function is overloaded to convert NUMBER, INTEGER, and other types to character strings. This shows how to use the function to convert a date to a string. You can supply an optional format string with the function. A few format strings are shown here.

FUNCTION TO_DATE(V VARCHAR2 [,format_model VARCHAR]) RETURN DATE;

This function converts a string to a DATE type. You can use a format model if the string format is not consistent with the DATE format in the database. The following converts a string to a DATE.

DECLARE
   D DATE;
   str VARCHAR2(30) := 'Wednesday August 06 2003';
   fmt VARCHAR2(30) := 'Day Month DD YYYY';
BEGIN
   D := TO_DATE(str, fmt);
END;

11.17.3. ADD_MONTHS

FUNCTION ADD_MONTHS(in_date DATE, months NUMBER) RETURN DATE;

This function adds or subtracts one or more months to a date argument.

If today is the last day of the month, such as October 31, and the following month has fewer days, November, this function returns November 30. The following declares DATE variables and initializes them to dates: advanced by one month and one month in the past.

same_day_next_month DATE := ADD_MONTHS(SYSDATE, 1);
same_day_last_month DATE := ADD_MONTHS(SYSDATE, -1);

11.17.4. LAST_DAY

FUNCTION LAST_DAY(in_date DATE) RETURN DATE;

This function returns the last day of the current month relative to IN_DATE. The following returns the last day of the current month.

v_date := LAST_DAY(SYSDATE);

Select all professors who have been hired any time in the current month. If the current day is July 20, we want all rows where

hire_date  >= July 1 at time 00:00:00

• Subtract a month from the current day with ADD_MONTHS.

• Get the last day of that month with LAST_DAY.

• Add a day, which yields the first day of the current month.

• TRUNC gives time 00:00:00 on the first of the current month.

The beginning of time for the current month is:

SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -1))+1)
FROM dual;

All professors hired this month are:

SELECT prof_name, hire_date
FROM   professors
WHERE  hire_date >=
       TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))+1);

Let's compute the last day of the previous month.

v_date := LAST_DAY(ADD_MONTHS(SYSDATE, -1));

The value for V_DATE will include hours, minutes, and seconds, for example:

30-JUN-2003 14:32:00

If we add a date and truncate, the result is 1-JULY-2003 00:00:00. We can then select all events in the current month with the qualifier:

WHERE some_date_column >= TRUNC(v_date+1)

We can filter all events previous to the current month with

WHERE some_date_column < TRUNC(v_date+1)

The beginning of the current month is always:

TRUNC(LAST_DAY(ADD_MONTHS(sysdate, -1))+1)

11.17.5. MONTHS_BETWEEN

FUNCTION MONTHS_BETWEEN (date1 DATE, date2 DATE) RETURN NUMBER;

This function returns the number of months between two dates. A fractional part is included in the returned number. The following returns the number of months between January 1 and July 1.

This block assigns N the value of 6.0.

DECLARE
   D1 DATE;
   D2 DATE;
   N  NUMBER(4,2);
BEGIN
   D1:= to_date('1-Jul-2004','DD-MON-YYYY'),
   D2:= to_date('1-Jan-2004','DD-MON-YYYY'),
   N := MONTHS_BETWEEN(D1, D2);  -- N is 6
END;

11.17.6. NEW_TIME

FUNCTION NEW_TIME (in_date DATE, time_zone VARCHAR2,
    time_zone_of_result VARCHAR2) RETURN DATE;

This function evaluates IN_DATE (relative to a time zone—TIME_ZONE) and returns a new time (relative to TIME_ZONE_OF_RESULT).

For example, take the current time zone, assuming Eastern Standard Time, and convert it to Pacific Standard Time.

pst_date := NEW_TIME(SYSDATE, 'EST','PST'),

Convert 12 noon today, Eastern Standard Time, to GMT.

DECLARE
  today          DATE:= sysdate;
  converted_time DATE;
BEGIN
    -- set converted_time to 12 noon today.
    converted_time := TRUNC(today) + 1/2;

    -- convert this to GMT time.
    converted_time := NEW_TIME(converted_time, 'EDT','GMT'),

    dbms_output.put_line(converted_time);
END;

The following table lists the string abbreviations for time conversions.

11.17.7. NEXT_DAY

FUNCTION NEXT_DAY(in_date DATE, weekday VARCHAR2) RETURN DATE;

This function can be used, for example, to return the DATE associated with next Monday. The parameter IN_DATE can be any date. We can compute the first day of a month with LAST_MONTH and LAST_DAY. Incorporation of this function enables computation of the first Monday of a month. The values for WEEKDAY are

• 'MONDAY'

• 'TUESDAY'

• 'WEDNESDAY'

• 'THURSDAY'

• 'FRIDAY'

• 'SATURDAY'

• 'SUNDAY'

What day is the following Monday?

v_date := NEXT_DAY(SYSDATE, 'MONDAY'),

Get the first Tuesday of last month.

DECLARE
    start_of_last_month DATE;
    first_tuesday       DATE;
BEGIN
    start_of_last_month :=
            TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE, -2))+1);

    first_tuesday :=
            NEXT_DAY(start_of_last_month, 'TUESDAY'),
END;

11.17.8. ROUND, TRUNC

FUNCTION TRUNC(in_date DATE) RETURN DATE;
FUNCTION ROUND(in_date DATE) RETURN DATE;

Time of day starts with hours, minutes, seconds at:

00:00:00

If you TRUNCATE date, the result is that date at zero hours, minutes, and seconds. This is effectively the start of the day. The following declares a DATE variable and initializes it to the start of the current day.

start_of_day DATE := TRUNC(SYSDATE);

The start of tomorrow is:

start_of_tomorrow DATE := TRUNC(SYSDATE + 1);

If a DATE variable is set to a time frame within the current day, the following is TRUE:

start_of_day <= variable < start_of_tomorrow

Based on this, if you ROUND a DATE the result is one of the following.

start_of_today
start_of_tomorrow

ROUND always returns the truncation of the current day or the equivalent of the truncation of that day plus 1. If the time is 12 noon or greater, it rounds to the start of the next day.