Oracle Date Functions and Operators.

In this tutorial we will see how to see current date in Oracle, how to manipulate dates, how to show dates in different formats and we will discuss the different functions available in Oracle for dates manipulation.

We will cover all important scenarios which a user might face while working with Oracle.

Functions to View Current Date and Time

To see the system date and time in Oracle, we can use the following functions :

CURRENT_DATE    :returns the current date in the session time zone, in a value in the Gregorian calendar of datatype
                                   DATE
SYSDATE                 :Returns the current date and time.
SYSTIMESTAMP    :The SYSTIMESTAMP function returns the system date, including fractional seconds and time zone
                                    of the database. The return type is TIMESTAMP WITH TIME ZONE.

SYSDATE Example

To see the current system date and time we can use the most simple and widely used SYSDATE function.

To see system date and time give the following query.

select sysdate from dual;

SYSDATE
-------
8-AUG-03

The format in which the date is displayed depends on NLS_DATE_FORMAT parameter.

For example set the NLS_DATE_FORMAT to the following format

alter session set NLS_DATE_FORMAT=’DD-MON-YYYY HH:MIpm’;

Then give the give the following statement

select sysdate from dual;

SYSDATE
------------------
8-AUG-2003 03:05pm

The default setting of NLS_DATE_FORMAT is DD-MON-YY

CURRENT_DATE Example

To see the current system date and time with time zone use CURRENT_DATE function

ALTER SESSION SET TIME_ZONE = '-4:0';
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-04:00          22-APR-2003 14:15:03

ALTER SESSION SET TIME_ZONE = '-7:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
-07:00          22-APR-2003 09:15:33

SYSTIMESTAMP Example

To see the current system date and time with fractional seconds with time zone give the following statement

select systimestamp from dual;

SYSTIMESTAMP
-------------------------------
22-APR-03 08.38.55.538741 AM -07:00

Next, How to view Date and Time in different formats.

Formatting Oracle Dates in different Formats

By default, Oracle will display the dates in DD-MON-YY format. If you want to see dates in other format then you can do so by using TO_CHAR() function. The TO_CHAR() function is used to convert date and time into a character string according to the given format. Oracle has provided several formats to convert the date and time to meet all user requirements

To translate the date into a different format string you can use TO_CHAR function with date format. For example, to see the current day you can give the following query

Select to_char(sysdate,’DAY’)”Today” FROM DUAL;

TODAY
-------
THURSDAY

Like this “DAY” format model there are many other date format models available in Oracle. The following table list date and time format models.

suffixes

For example to see the today’s date in the following format

Friday, 7th March, 2014 

Give the following statement

select to_char(sysdate,’Day, ddth Month, yyyy’)”Today” from dual;

TODAY
------------------------
Friday, 7th March, 2014 

For example you want to see hire dates of all employee in the following format

Friday, 8th August, 2003 

Then give the following query.

select to_char(hire_date,’Day, ddth Month, yyyy’) from emp;

Next, to translate a character value, which is in format other than the default date format, into a date value you can use TO_DATE function with date format to date

TO_DATE Example

TO_DATE() function is used to convert strings into date values. For example you want to see what was the day on 15-aug-1947. For this purpose, we will use the TO_DATE()  function to first convert the string into date value and then pass on this value to TO_CHAR() function to extract day.

select to_char(to_date(’15-aug-1947’,’dd-mon-yyyy’),’Day’)
                                         from dual;

TO_CHAR(
--------
Friday

To see how many days have passed since 15-aug-1947 then give the following query

select sysdate-to_date(’15-aug-1947’,’dd-mon-yyyy’) from dual;

Now we want to see which date will occur after 45 days from now

select sysdate+45 from dual;

SYSDATE
-------
06-JUN-2003

ADD_MONTHS

To see which date will occur after 6 months from now, we can use ADD_MONTHS function

Select ADD_MONTHS(SYSDATE,6) from dual;

ADD_MONTHS
----------
22-OCT-2003

MONTHS_BETWEEN

To see how many months have passed since  a particular date, use the MONTHS_BETWEEN function.

For Example, to see how many months have passed since 15-aug-1947, give the following query.

select months_between(sysdate,to_date(’15-aug-1947’))
                             from dual;

Months
------
616.553

To eliminate the decimal value use truncate function

select trunc(months_between(sysdate,to_date(’15-aug-1947’)))
                             from dual;

Months
------
616

LAST_DAY

To see the last date of the month of a given date, Use LAST_DAY function.

select LAST_DAY(sysdate) from dual;

LAST_DAY
--------
31-AUG-2003

NEXT_DAY

To see when a particular day is coming next ,  use the NEXT_DAY function.

For Example to view when next Saturday is coming, give the following query

select next_day(sysdate) from dual;

NEXT_DAY
-----------
09-AUG-2003

EXTRACT

An EXTRACT datetime function extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation

The syntax of EXTRACT function is

EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)

Example
The following demonstrate the usage of EXTRACT function to extract year from current date.

select extract(year from sysdate) from dual;

EXTRACT
-------
2003

In this lesson, we have learnt how to use TO_DATE() and TO_CHAR() function. Besides this, we have also learnt how to convert dates into different formats and also learnt how to convert character strings to dates.

See also:

Oracle SQL Functions

Oracle NVL function