How to operate on Date and perform Date conversion in Oracle

Date Functions and Operators.

To see the system date and time 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 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

DATE FORMAT MODELS

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

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

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 format models.

suffixes

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

Friday, 8th August, 2003 

Give the following statement

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

TODAY

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

Friday, 8th August, 2003 

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;

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. The 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 15-aug-1947, use the MONTHS_BETWEEN function.

Select months_between(sysdate,to_date(’15-aug-1947’))

                             from dual;

Months

------

616.553

To eliminate the decimal value use truncate function

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 the next Saturday is coming, use the NEXT_DAY function.

select next_day(sysdate) from dual;

NEXT_DAY

-----------

09-AUG-2003

EXTRACT (Oracle 9i, 10g only)

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