Oracle Date Functions

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

Date Functions in Oracle

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.

CURRENT_DATE Function in Oracle

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

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.

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.

SYSDATE Function in Oracle

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

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.

EXTRACT Function in Oracle

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

LAST_DAY Function in Oracle

Returns the last day of the specified month.

MONTHS_BETWEEN Function in Oracle

Returns the number of month between specified to to date.

NEW_TIME Function in Oracle

Converts the timezone of the date.

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.

Round Function with dates in Oracle

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

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.

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.

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.