oracle date functions

Date/Time Arithmetic with Oracle 9/10

Date and Time information can be stored in Oracle using two different column’s datatype – DATE or TIMESTAMP.

Subtraction using DATE:

DATE datatype stores Month, Date, Year, Century, Hours, Minutes and Seconds. This datatype cannot be used to get granular time intervals between two time events.

DEFINE Time1=TO_DATE('03.12.2004:10:34:24','DD.MM.YYYY:HH24:MI:SS')


SELECT TO_CHAR(&Time1,'DD.MM.YYYY:HH24:MI:SS') "Time1", TO_CHAR(&Time2,'DD.MM.YYYY:HH24:MI:SS') "Time2", &Time2 - &Time1 "Time2-Time1", TRUNC(86400*(&Time2-&Time1))-60*(TRUNC((86400*(&Time2-&Time1))/60)) "Sec", TRUNC((86400*(&Time2-&Time1))/60)-60*(TRUNC(((86400*(&Time2-&Time1))/60)/60)) "Min", TRUNC(((86400*(&Time2-&Time1))/60)/60)-24*(TRUNC((((86400*(&Time2-&Time1))/60)/60)/24))"Hrs", TRUNC((((86400*(&Time2-&Time1))/60)/60)/24) "Days"FROM dual;

Time1 Time2 Time2-Time1 Sec Min Hrs Days------------------- ------------------- ----------- ---------- ---------- ---------- ----------03.12.2004:10:34:24 17.12.2010:10:34:12 2204.99986 48 59 23 2204

Subtraction using TIMESTAMP:

TIMESTAMP datatype is an expansion of the DATE datatype to include fractional seconds.

DEFINE Time1=TO_TIMESTAMP('03.12.2004:10:34:24:05','DD.MM.YYYY:HH24:MI:SS:FF')

DEFINE Time2=TO_TIMESTAMP('17.12.2010:10:34:12:03','DD.MM.YYYY:HH24:MI:SS:FF')

SELECT &Time2 - &Time1 “Time2–Time1” FROM dual;

Time2-Time1-------------------------------+000002204 23:59:47.980000000

Converting DATE to TIMESTAMP:

To convert the existing DATE datatype to TIMESTAMP datatype, we could use the CAST function. Since the DATE does not include fractional seconds, the CAST function would default it to zeros and the display is defaulted to the default timestamp format (NLS_TIMESTAMP_FORMAT).



SELECT CAST(&Time2 AS TIMESTAMP) – CAST(&Time1 AS TIMESTAMP “Time2–Time1” FROM dual;

Time2-Time1----------------------------+000002204 23:59:47.000000

NEXT_DAY (date, char)

NEXT_DAY returns the date of the first weekday named by char that is later than date. The return type is always DATE, regardless of the datatype of date. The argument char must be a day of the week in the date language of your session, either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version. Any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument date.

SELECT TO_CHAR(NEXT_DAY(SYSDATE,'MON'),'DD.MM.YYYY') "Next Monday" FROM dual;

Next Monday-----------08.03.2010

LAST_DAY(date)

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the datatype of date.

SELECT SYSDATE, LAST_DAY(SYSDATE) "Last", LAST_DAY(SYSDATE) - SYSDATE "Days Left" FROMdual;

SYSDATE Last Days Left--------- --------- ----------04-MAR-10 31-MAR-10 27

Get the last date of a month:

SELECT SYSDATE, LAST_DAY(TO_DATE('02','MM')) "Last Day" FROM dual;

SYSDATE Last Day-------------------- ---------3/4/2010 11:51:14 PM 28-FEB-10

GREATEST(date1, date2, date3, ….)

GREATEST returns the latest date within the list of dates provided.

SELECT GREATEST(SYSDATE, SYSDATE+1, SYSDATE-1) “GREATEST” FROM dual;

GREATEST--------------------3/9/2010 11:59:13 AM

CREATE TABLE example_tb (datecol1 DATE,datecol2 DATE,datecol3 DATE)

INSERT INTO example_tb VALUES (SYSDATE+14, SYSDATE-10, SYSDATE-24);INSERT INTO example_tb VALUES (SYSDATE-25, SYSDATE, SYSDATE+11);INSERT INTO example_tb VALUES (SYSDATE-6, SYSDATE-7, SYSDATE-9);

SELECT X.*, GREATEST(X.datecol1, X.datecol2, X.datecol3) "Greatest" FROM example_tb X;

DATECOL1 DATECOL2 DATECOL3 Greatest --------------------- --------------------- --------------------- ---------------------3/22/2010 12:08:09 PM 2/26/2010 12:08:09 PM 2/12/2010 12:08:09 PM 3/22/2010 12:08:09 PM2/11/2010 12:08:10 PM 3/8/2010 12:08:10 PM 3/19/2010 12:08:10 PM 3/19/2010 12:08:10 PM3/2/2010 12:08:11 PM 3/1/2010 12:08:11 PM 2/27/2010 12:08:11 PM 3/2/2010 12:08:11 PM

LEAST(date1, date2, date3, ….)

LEAST returns the earliest date within the list of dates provided.

SELECT SYSDATE, LEAST(SYSDATE, SYSDATE+1, SYSDATE-1) “LEAST” FROM dual;

SYSDATE LEAST-------------------- -------------------3/8/2010 1:59:27 PM 3/7/2010 1:59:27 PM

SELECT X.*, LEAST(X.datecol1, X.datecol2, X.datecol3) "Least" FROM example_tb X;

DATECOL1 DATECOL2 DATECOL3 Least --------------------- --------------------- --------------------- ---------------------3/22/2010 12:08:09 PM 2/26/2010 12:08:09 PM 2/12/2010 12:08:09 PM 2/12/2010 12:08:09 PM2/11/2010 12:08:10 PM 3/8/2010 12:08:10 PM 3/19/2010 12:08:10 PM 2/11/2010 12:08:10 PM3/2/2010 12:08:11 PM 3/1/2010 12:08:11 PM 2/27/2010 12:08:11 PM 2/27/2010 12:08:11 PM

MAX(date)

MAX is a GROUP function returning the latest date.

SELECT MAX(X.datecol1) "Max of Datecol1" FROM example_tb X;

Max of Datecol1---------------------3/22/2010 12:08:09 PM

MIN(date)

MIN is a GROUP function returning the earliest date.

SELECT MIN(X.datecol1) "Min of Datecol1" FROM example_tb X;

Min of Datecol1---------------------2/11/2010 12:08:10 PM

ADD_MONTHS(date, integer)

ADD_MONTHS adds the number of integer months to the date.

SELECT SYSDATE, ADD_MONTHS(SYSDATE,2) "X", ADD_MONTHS(SYSDATE,-2) "Y" FROM dual;

SYSDATE X Y------------------- ------------------- -------------------3/8/2010 2:00:43 PM 5/8/2010 2:00:43 PM 1/8/2010 2:00:43 PM

MONTHS_BETWEEN(date, date)

MONTHS_BETWEEN returns the months between the two dates.

SELECT MONTHS_BETWEEN(SYSDATE+365, SYSDATE-365) "Months Between" FROM dual;

Months Between--------------24

ROUND(date, <format>)

ROUND returns the date rounded to the unit specified by the format. If format is not provided, the date is rounded to the nearest date.

SELECT SYSDATE, ROUND(SYSDATE) "No Unit Round", ROUND(SYSDATE,'MM') "Unit Round" FROM dual; -- (MM/Month - rounds up on the 16th day)

SYSDATE No Unit Round Unit Round------------------- -------------- ----------3/8/2010 1:54:26 PM 3/9/2010 3/1/2010

TRUNC(date, <format>)

TRUNC returns the date truncated to the unit specified by the format.

SELECT SYSDATE, TRUNC(SYSDATE) "No Unit Trunc", TRUNC(SYSDATE,'HH24') "Unit Trunc" FROM dual;

SYSDATE No Unit Trunc Unit Trunc------------------- -------------- -------------------3/8/2010 1:56:04 PM 3/8/2010 3/8/2010 1:00:00 PM

oracle date functions

Technology