Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
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.
1 2 3 4 5 6 7 8 | 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.
1 2 3 4 5 6 7 8 | 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.
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..
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.
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.
1 2 3 4 5 6 7 8 | 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.
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.
1 2 3 4 5 6 7 | 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.
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.
1 2 3 4 5 | 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.
1 2 3 4 5 | 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.
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.
1 2 3 4 5 | 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.
1 2 3 4 5 6 7 8 | 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' |
