11.17 Date Functions | Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

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.

Sample Format Model	Output from TO_CHAR
`TO_CHAR(SYSDATE,'Day')`	`Wednesday`
`TO_CHAR(SYSDATE,'Mon');`	`Aug`
`TO_CHAR(SYSDATE,'YYYY');`	`2003`
`TO_CHAR(SYSDATE,'Day Month YYYY');`	`Wednesday August 2003`
`TO_CHAR(SYSDATE,'DD-MON-YYYY');`	`06-AUG-2003`
`TO_CHAR(SYSDATE,'Day Month DD, YYYY');`	`Wednesday August 06, 2003`

 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.

Time Zone	Conversion String
Atlantic Standard or Daylight Time	AST, ADT
Bering Standard or Daylight Time	BST, BDT
Central Standard or Daylight Time	CST, CDT
Eastern Standard or Daylight Time	EST, EDT
Greenwich Mean Time	GMT
Alaska-Hawaii Standard Time or Daylight Time	HST, HDT
Mountain Standard or Daylight Time	MST, MDT
Newfoundland Standard Time	NST
Pacific Standard or Daylight Time	PST, PDT
Yukon Standard or Daylight Time	YST, YDT

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.