Oracle Date Functions

Yusuf SEZER

5 years ago

This article contains information about oracle date functions such as sysdate, last_day, add_months, extract.

Oracle Date Functions

Oracle has several functions to operate on date and time data.

ADD_MONTHS Function in Oracle

Adds specified number of months to the specified date value.

SELECT ADD_MONTHS(15-Jan-19', 4) AS RESULT FROM DUAL;
Result: '15-May-19'

1 2	SELECT ADD_MONTHS(15-Jan-19', 4) AS RESULT FROM DUAL; Result: '15-May-19'

CURRENT_DATE Function in Oracle

Returns the date information of the current session. If you change the time zone the result will change.

SELECT CURRENT_DATE AS Result FROM DUAL;
Result: 01/01/2020 01:00:00 AM

--Here we are changing time zone 0 to 3
ALTER SESSION SET TIME_ZONE = '3:0';

SELECT CURRENT_DATE AS Result FROM DUAL; 
Result: 01/01/2020 04:00:00 AM

SELECT CURRENT_DATE AS Result FROM DUAL;

Result: 01/01/2020 01:00:00 AM

--Here we are changing time zone 0 to 3

ALTER SESSION SET TIME_ZONE = '3:0';

SELECT CURRENT_DATE AS Result FROM DUAL;

Result: 01/01/2020 04:00:00 AM

CURRENT_TIMESTAMP Function in Oracle

Returns the date and time information of the current session. If you change the time zone the result will change.

SELECT CURRENT_DATE AS Result FROM DUAL;
Result:1-JAN-20 01.01.01.123456 PM 00:00

--Here we are changing time zone 0 to 3 
ALTER SESSION SET TIME_ZONE = '3:0'; 

SELECT CURRENT_DATE AS Result FROM DUAL;
Result:1-JAN-20 04.01.01.123456 PM 00:00

SELECT CURRENT_DATE AS Result FROM DUAL;

Result:1-JAN-20 01.01.01.123456 PM 00:00

--Here we are changing time zone 0 to 3

ALTER SESSION SET TIME_ZONE = '3:0';

SELECT CURRENT_DATE AS Result FROM DUAL;

Result:1-JAN-20 04.01.01.123456 PM 00:00

LOCALTIMESTAMP Function in Oracle

Its similar with CURRENT_TIMESTAMP Function. The difference is that the LOCALTIMESTAMP function returns a TIMESTAMP value but CURRENT_TIMESTAMP function returns a TIMESTAMP WITH TIME ZONE.

DBTIMEZONE Function in Oracle

Returns the timezone information of the database server. If you change the time zone of the database server, the result will change.

SELECT DBTIMEZONE AS RESULT FROM DUAL;
Possible Result: +00:00-> this may change according to your database server's timezone.

1 2	SELECT DBTIMEZONE AS RESULT FROM DUAL; Possible Result: +00:00-> this may change according to your database server's timezone.

SYSDATE Function in Oracle

Returns the current system date and time information of your local server where the database resides..

SELECT SYSDATE AS RESULT FROM DUAL;

1	SELECT SYSDATE AS RESULT FROM DUAL;

SYSTIMESTAMP Function in Oracle

Returns the current system date and time information. It includes fractional seconds and time zone information of your local server where the database resides.

SELECT SYSTIMESTAMP AS RESULT FROM DUAL;

1	SELECT SYSTIMESTAMP AS RESULT FROM DUAL;

EXTRACT Function in Oracle

It extracts the parts such as year, month, day and hour from date/time information.

SELECT EXTRACT(YEAR FROM DATE '2020-01-15')
Result: 2020

SELECT EXTRACT(MONTH FROM DATE '2020-01-15')
Result: 1

SELECT EXTRACT(DAY FROM DATE '2020-01-15')
Result: 15

SELECT EXTRACT(YEAR FROM DATE '2020-01-15')

Result: 2020

SELECT EXTRACT(MONTH FROM DATE '2020-01-15')

Result: 1

SELECT EXTRACT(DAY FROM DATE '2020-01-15')

Result: 15

LAST_DAY Function in Oracle

Returns the last day of the specified month.

SELECT LAST_DAY('15-JAN-20') AS RESULT FROM DUAL;
Result: '31-JAN-20'

1 2	SELECT LAST_DAY('15-JAN-20') AS RESULT FROM DUAL; Result: '31-JAN-20'

MONTHS_BETWEEN Function in Oracle

Returns the number of month between specified to to date.

SELECT MONTHS_BETWEEN(TO_DATE('01-15-2020','MM-DD-YYYY'),
TO_DATE('02-15-2020','MM-DD-YYYY') ) FROM DUAL; 
Result:-1 

SELECT MONTHS_BETWEEN(TO_DATE('02-15-2020','MM-DD-YYYY'),
TO_DATE('01-15-2020','MM-DD-YYYY') ) FROM DUAL; 
Result:1

SELECT MONTHS_BETWEEN(TO_DATE('01-15-2020','MM-DD-YYYY'),

TO_DATE('02-15-2020','MM-DD-YYYY') ) FROM DUAL;

Result:-1

SELECT MONTHS_BETWEEN(TO_DATE('02-15-2020','MM-DD-YYYY'),

TO_DATE('01-15-2020','MM-DD-YYYY') ) FROM DUAL;

Result:1

NEW_TIME Function in Oracle

Converts the timezone of the date.

SELECT NEW_TIME(TO_DATE('01-01-2020 01:00:00', 
'MM-DD-YY HH24:MI:SS'),'AST', 'ADT') "New Timezone" FROM DUAL;

1 2	SELECT NEW_TIME(TO_DATE('01-01-2020 01:00:00', 'MM-DD-YY HH24:MI:SS'),'AST', 'ADT') "New Timezone" FROM DUAL;

You can find timezones in the below table.

Timezone Value	TimeZone Description
AST	Atlantic Standard Time
ADT	Atlantic Daylight Time
BST	Bering Standard Time
BDT	Bering Daylight Time
CST	Central Standard Time
CDT	Central Daylight Time
EST	Eastern Standard Time
EDT	Eastern Daylight Time
GMT	Greenwich Mean Time
HST	Alaska-Hawaii Standard Time
HDT	Alaska-Hawaii Daylight Time
MST	Mountain Standard Time
MDT	Mountain Daylight Time
NST	Newfoundland Standard Time
PST	Pacific Standard Time
PDT	Pacific Daylight Time
YST	Yukon Standard Time
YDT	Yukon Daylight Time

NEXT_DAY Function in Oracle

Returns the first weekday after the specified date.

SELECT NEXT_DAY('23-Jan-20', 'TUESDAY') FROM DUAL;
Result: '28-Jan-20'

SELECT NEXT_DAY('14-Feb-20', 'THURSDAY') FROM DUAL;
Result: '20-Feb-20'

SELECT NEXT_DAY('23-Jan-20', 'TUESDAY') FROM DUAL;

Result: '28-Jan-20'

SELECT NEXT_DAY('14-Feb-20', 'THURSDAY') FROM DUAL;

Result: '20-Feb-20'

Round Function with dates in Oracle

It rounds the date to new value(new month, new year) according to the second parameter.

SELECT ROUND(TO_DATE ('15-JAN-2020'),'MONTH') "New Month",
ROUND(TO_DATE ('15-JAN-2020'),'YEAR') "New Year" FROM DUAL;
New Month      New Year
------------  ------------
01-FEB-2020   01-JAN-2021

SELECT ROUND(TO_DATE ('15-JAN-2020'),'MONTH') "New Month",

ROUND(TO_DATE ('15-JAN-2020'),'YEAR') "New Year" FROM DUAL;

New Month New Year

------------ ------------

01-FEB-2020 01-JAN-2021

Round Parameters	Valid formats
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

SESSIONTIMEZONE Function in Oracle

Returns current session’s time zone information.

SELECT SESSIONTIMEZONE FROM DUAL;
Result: +03:00->this is possible result. It changes according to the session's time zone.

1 2	SELECT SESSIONTIMEZONE FROM DUAL; Result: +03:00->this is possible result. It changes according to the session's time zone.

TRUNC Function with dates in Oracle

It truncs date according to the specified parameter. For example if you specify YEAR as paramater, it truncs the year of the date as seen below.

SELECT TRUNC(TO_DATE('05-MAY-20'), 'YEAR') FROM DUAL;
Result: '01-JAN-20'

SELECT TRUNC(TO_DATE('05-MAY-20'), 'MONTH') FROM DUAL;
Result: '01-MAY-20'

SELECT TRUNC(TO_DATE('05-MAY-20'), 'YEAR') FROM DUAL;

Result: '01-JAN-20'

SELECT TRUNC(TO_DATE('05-MAY-20'), 'MONTH') FROM DUAL;

Result: '01-MAY-20'

Other parameter you can use with Trunc function:

Trunc Parameters	Valid formats
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

TZ_OFFSET Function in Oracle

Returns timezone offset information of a value.

SELECT TZ_OFFSET('US/Arizona') FROM DUAL;
Result: '-07:00'

SELECT TZ_OFFSET('-07:00') FROM DUAL;
Result: '-07:00'

SELECT TZ_OFFSET(sessiontimezone) FROM DUAL;
Result: '+03:00'

SELECT TZ_OFFSET('US/Arizona') FROM DUAL;

Result: '-07:00'

SELECT TZ_OFFSET('-07:00') FROM DUAL;

Result: '-07:00'

SELECT TZ_OFFSET(sessiontimezone) FROM DUAL;

Result: '+03:00'