Oracle Date Functions
 
Version 10.1

Date

Current Date
    CURRENT_DATE
SYSDATE
SELECT CURRENT_DATE FROM dual;

SELECT SYSDATE FROM dual;

Current Time
    CURRENT_TIME
SELECT CURRENT_TIME FROM dual;

Formats
    J     Julian Date

-- Day
D
DD
DAY

-- Month
MM
MON

-- Year
YY
YYYY
RR
RRRR

-- Fill Mode
FM

Formats That Can Be Used With ROUND And TRUNC Functions
    - One greater than the first two digits of a four-digit year
CC
SCC

- Year (rounds up on July 1)
SYYYY
YYYY
YEAR
SYEAR
YYY
YY
Y 

- ISO Year
IYYY
IY
IY
I

- Quarter (rounds up on 16th day of the 2nd month of the quarter)
Q

- Month (rounds up on the sixteenth day)
MONTH
MON
MM
RM

- Same day of the week as the first day of the year
WW

- Same day of the week as the first day of the ISO year
IW

- Same day of the week as the first day of the month
W

- Day
DDD
DD
J

- Starting day of the week
DAY
DY
D

- Hours
HH
HH12
HH24

- Minute
MI
 
+ AND -
+     <date> + <integer>
SELECT SYSDATE + 1 FROM dual;
-     <date> - <integer>
SELECT SYSDATE - 1 FROM dual;
 
ADD_MONTHS
Add A Month To A Date     ADD_MONTHS(<date>, <number of months_integer>
SELECT add_months(SYSDATE, 2) FROM dual;
 
CURRENT_DATE
Returns the current date of the server as a value in the Gregorian calendar of datatype DATE      
col sessiontimezone format a30

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-5:0';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

SELECT sessiontimezone, current_date
FROM dual;

ALTER SESSION SET TIME_ZONE = '-7:0';

SELECT sessiontimezone, current_date
FROM dual;
 
DUMP
Returns The Number Of Bytes And Datatype Of A Value     DUMP(<value>)
SELECT DUMP(SYSDATE) FROM dual;
 
EXTRACT
Extracts and returns the value of a specified datetime field from a datetime or interval value expression     Values That Can Be Extracted:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
TIMEZONE_HOUR
TIMEZONE_MINUTE
TIMEZONE_REGION
TIMEZONE_ABBR

EXTRACT (<type> FROM <datetime | interval>)
SELECT EXTRACT(YEAR FROM DATE '2005-01-04') FROM dual;

CREATE TABLE extract_test (
person_id   NUMBER(3),
hire_date   TIMESTAMP WITH TIME ZONE)
TABLESPACE data_sml;

INSERT INTO extract_test VALUES (100, SYSTIMESTAMP+1);
INSERT INTO extract_test VALUES (200, SYSTIMESTAMP+34);

SELECT EXTRACT(TIMEZONE_HOUR FROM hire_date)
FROM extract_test
 
GREATEST
Return the Latest Date     LEAST(<date>, <date>, <date>, ...)
CREATE TABLE t (
datecol1 DATE,
datecol2 DATE,
datecol3 DATE)
PCTFREE 0
TABLESPACE data_sml;

INSERT INTO t VALUES (SYSDATE+23, SYSDATE-10, SYSDATE-24);
INSERT INTO t VALUES (SYSDATE-15, SYSDATE, SYSDATE+15);
INSERT INTO t VALUES (SYSDATE-7, SYSDATE-18, SYSDATE-9);
COMMIT;

SELECT * FROM t;

SELECT GREATEST(datecol1, datecol2, datecol3)
FROM t;
 
LAST_DAY
Returns The Last Date Of A Month     LAST_DAY(<date>)
SELECT * FROM t;

SELECT LAST_DAY(datecol1) FROM t;
 
LEAST
Return the Earliest Date     LEAST(<date>, <date>, <date>, ...)
SELECT * FROM t;

SELECT LEAST(datecol1, datecol2, datecol3) FROM t;
 
MAX
Return the Latest Date     MAX(<date>)
SELECT * FROM t;

SELECT MAX(datecol1) FROM t;
 
MIN
Return the Earliest Date     MIN(<date>)
SELECT * FROM t;

SELECT MIN(datecol1) FROM t;
 
MONTHS_BETWEEN
Returns The Months Separating Two Dates     MONTHS_BETWEEN(<latest_date>, <earliest_date>)
SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) FROM dual;

SELECT MONTHS_BETWEEN(SYSDATE-365, SYSDATE+365) FROM dual;
 
NEW_TIME
Returns the date and time in time zone zone2 when date and time in time zone zone1 are date.      Before using this function, you must set the NLS_DATE_FORMAT parameter to display 24-hour time.
SELECT NEW_TIME(TO_DATE( '11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;

ALTER SESSION SET NLS_DATE_FORMAT =
'DD-MON-YYYY HH24:MI:SS';

SELECT NEW_TIME(TO_DATE('11-10-99 01:23:45',
'MM-DD-YY HH24:MI:SS'), 'AST', 'PST') "New Date and Time"
FROM dual;
 
NEXT_DAY
Date of next specified date following a date     NEXT_DAY(<date>, <day of the week>)

Options are SUN, MON, TUE, WED, THU, FRI, and SAT
SELECT NEXT_DAY(SYSDATE, 'FRI') FROM dual;
 
NUMTODSINTERVAL
Converts a number to an INTERVAL DAY TO SECOND literal     NUMTODSINTERVAL(n, <interval_unit>)
SELECT NUMTODSINTERVAL(120, 'DAY') FROM dual;

SELECT NUMTODSINTERVAL(1200, 'HOUR') FROM dual;

SELECT NUMTODSINTERVAL(12000, 'MINUTE') FROM dual;

SELECT NUMTODSINTERVAL(120000, 'SECOND') FROM dual;
 
NUMTOYMINTERVAL
Converts n to an INTERVAL YEAR TO MONTH literal.     NUMTOYMINTERVAL(n, <interval_unit>)
conn hr/hr

SELECT last_name, hire_date, salary, SUM(salary)
OVER (ORDER BY hire_date
RANGE NUMTOYMINTERVAL(1, 'YEAR') PRECEDING) AS t_sal
FROM employees;
 
ROUND
Returns date rounded to the unit specified by the format model. If you omit the format, the date is rounded to the nearest day     ROUND(<date_value>, <format>)
SELECT ROUND(TO_DATE ('27-OCT-00'),'YEAR') NEW_YEAR
FROM dual;
 
SYSDATE
Returns the current session DateTime     SYSDATE
SELECT SYSDATE FROM dual;
 
TO_CHAR
Converts  DATE and  TIMESTAMP to VARCHAR2  with the specified format     TO_DATE(<date_string>, <format mask>, <NLS_PARAMETER>)
SELECT TO_CHAR(datecol1, 'DAY-MONTH-YY') COL1,
TO_CHAR(datecol2, 'D-MM/YYYY HH24:MI:SS') COL2,
TO_CHAR(datecol2, 'YYYY-MON-DD HH24') COL3
FROM t;
Demo of FM Formatting From Tom Kyte (Oracle Magazine 3-4/2004)     SELECT TO_CHAR(dt, 'HH:MI AM') A,
       TO_CHAR(dt, 'FMHH:MI AM') B,
       TO_CHAR(dt, 'FMHHFM:MI AM') C
FROM (
  SELECT TO_DATE('09:01 am', 'HH:MI AM') dt
  FROM dual);
 
TO_DATE
Convert A String With Default Format To A Date     TO_DATE(<string>)
SELECT TO_DATE('01-JAN-2004') FROM dual;
Convert A String With A Non-Default Format To A Date     TO_DATE(<string>, <format mask>)
SELECT TO_DATE('01/01/2004', 'MM/DD/YYYY') FROM dual;
Convert A String With A Non-Default Format And Specify The Language     TO_DATE(<string>, <format mask>)
SELECT TO_DATE('January 12, 2005, 11:03 A.M.', 'MONTH DD, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM dual;
Convert A String With A Non-Default Format And Specify The Language     TO_DATE(<date_string>, <format mask>, <NLS_PARAMETER>)
ALTER SESSION SET NLS_TERRITORY = 'JAPAN';

SELECT TO_DATE('January 12, 2005, 11:03 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL;

ALTER SESSION SET NLS_TERRITORY = 'AMERICA';
Convert A String To 24 Hour Time     TO_DATE(<date_string>, <format mask>)
SELECT TO_CHAR(SYSDATE, 'MM/DD/YY HH24:MI:SS') FROM dual;
 
TO_DSINTERVAL
Converts A String To An INTERVAL DAY TO SECOND DataType     TO_DSINTERVAL(<date_string>, <format mask>, <NLS_PARAMETER>)
conn hr/hr

SELECT 'employee_id, last_name
FROM employees
WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
<= DATE '1990-01-01';
 
TO_NCHAR
Converts a DATE or  TIMESTAMP from the database character set to the National Character Set specificed     TO_NCHAR(<date_string | interval>, <format mask>, <NLS_PARAMETER>)
conn oe/oe

SELECT TO_NCHAR(order_date)
FROM orders
WHERE order_status > 9;
 
TO_YMINTERVAL
Converts a string to an Interval Year to Month data type     TO_YM_INTERVAL(<date_string>)
conn hr/hr

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
FROM employees;
 
TRUNC
Convert a date to the date at midnight     TRUNC(<date_time>)
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS')
FROM dual;

SELECT TO_CHAR(TRUNC(SYSDATE), 'DD-MON-YYYY HH:MI:SS')
FROM dual;
 
VSIZE
Returns The Number Of Bytes Required By A Value     VSIZE(<value>)
SELECT VSIZE(SYSDATE) FROM dual;
 
Date Calculations
Returns A Day A Specified Number Of Days In The Future Skipping Weekends    

CREATE OR REPLACE FUNCTION business_date (start_date DATE,
Days2Add NUMBER) RETURN DATE IS

Counter  NATURAL := 0;
CurDate  DATE := start_date;
DayNum   POSITIVE;
SkipCntr NATURAL := 0;

BEGIN
  WHILE Counter < Days2Add
  LOOP
    CurDate := CurDate+1;
    DayNum := TO_CHAR(CurDate, 'D');

    IF DayNum BETWEEN 2 AND 6 THEN
      Counter := Counter + 1;
    ELSE
      SkipCntr := SkipCntr + 1;
    END IF;
  END LOOP;
  RETURN start_date + Counter + SkipCntr;
END business_date;
/
Returns The First Day Of A Month     CREATE OR REPLACE FUNCTION fday_ofmonth(value_in DATE)
RETURN DATE IS

vMo VARCHAR2(2);
vYr VARCHAR2(4);

BEGIN
  vMo := TO_CHAR(value_in, 'MM');
  vYr := TO_CHAR(value_in, 'YYYY');
  RETURN TO_DATE(vMo || '-01-' || vYr, 'MM-DD-YYYY');

EXCEPTION
  WHEN OTHERS THEN
    RETURN TO_DATE('01-01-1900', 'MM-DD-YYYY');

END fday_ofmonth;
/
 
Time Calculations
Returns The Number Of Seconds Between Two Date-Time Values     CREATE OR REPLACE FUNCTION time_diff (
DATE_1 IN DATE, DATE_2 IN DATE) RETURN NUMBER IS

NDATE_1 NUMBER;
NDATE_2 NUMBER;
NSECOND_1 NUMBER(5,0);
NSECOND_2 NUMBER(5,0);

BEGIN
  -- Get Julian date number from first date (DATE_1)
  NDATE_1 := TO_NUMBER(TO_CHAR(DATE_1, 'J'));

  -- Get Julian date number from second date (DATE_2)
  NDATE_2 := TO_NUMBER(TO_CHAR(DATE_2, 'J'));

  -- Get seconds since midnight from first date (DATE_1)
  NSECOND_1 := TO_NUMBER(TO_CHAR(DATE_1, 'SSSSS'));

  -- Get seconds since midnight from second date (DATE_2)
  NSECOND_2 := TO_NUMBER(TO_CHAR(DATE_2, 'SSSSS'));

  RETURN (((NDATE_2 - NDATE_1) * 86400)+(NSECOND_2 - NSECOND_1));
END time_diff;
/