Wednesday , April 21 2021

Oracle Date Functions

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.

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 ValueTimeZone Description
ASTAtlantic Standard Time
ADTAtlantic Daylight Time
BSTBering Standard Time
BDTBering Daylight Time
CSTCentral Standard Time
CDTCentral Daylight Time
ESTEastern Standard Time
EDTEastern Daylight Time
GMTGreenwich Mean Time
HSTAlaska-Hawaii Standard Time
HDTAlaska-Hawaii Daylight Time
MSTMountain Standard Time
MDTMountain Daylight Time
NSTNewfoundland Standard Time
PSTPacific Standard Time
PDTPacific Daylight Time
YSTYukon Standard Time
YDTYukon 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 ParametersValid formats
YearSYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO YearIYYY, IY, I
QuarterQ
MonthMONTH, MON, MM, RM
WeekWW
IWIW
WW
DayDDD, DD, J
Start day of the weekDAY, DY, D
HourHH, HH12, HH24
MinuteMI

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 ParametersValid formats
YearSYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO YearIYYY, IY, I
QuarterQ
MonthMONTH, MON, MM, RM
WeekWW
IWIW
WW
DayDDD, DD, J
Start day of the weekDAY, DY, D
HourHH, HH12, HH24
MinuteMI

TZ_OFFSET Function in Oracle

Returns timezone offset information of a value.

Yusuf SEZER
Author: Yusuf SEZER

About Yusuf SEZER

Leave a Reply

Your email address will not be published. Required fields are marked *