Working with Dates in Oracle

Oracle DATE data type could be used to store both date and time parts. Does this mean it always stores both date and time parts or do we have to explicitly store time part? If it automatically stores time part, why time part is not displayed when I query dates in SQL developer or SQL plus? Can I use arithmetic functions directly with date fields? If yes, does the result contains value in days or months or years? How do we extract a specific part (i.e. day or year or secs etc) from date field?

This article is aimed to answer most of these questions.

Storage

Oracle DATE data type by default stores both date and time parts.  

The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

While inserting data, if time part is ignored then it will store zeros (i.e.  00:00:00 A.M.) in time part.  Remember, DATE data type does not store nano seconds.. if we need fraction of seconds to be stored, then we need to use timestamp.

We could use DUMP function to get the storage details

select dump (sysdate) from dual;

DUMP(SYSDATE)
_________________________________________
Typ=13 Len=8: 7,218,11,26,14,21,24,0

The above result indicates that data is stored as 8 bytes field.

Display

Oracle DATE fields are displayed as per the NLS_DATE_FORMAT settings in oracle database.

select * from SYS.NLS_DATABASE_PARAMETERS where parameter='NLS_DATE_FORMAT';

To display date in different format, change the value for NLS_DATE_FORMAT

Ex. alter session set nls_date_format='DD/MM/YYYY HH.MI.SS';

Or

dbms_session.set_nls('nls_date_format', 'DD/MM/YYYY HH.MI.SS');

For changing at database level, use ALTER SYSTEM.

Date Arithmetic Functions

In order to understand how dates could be used in the arithmetic functions like add, multiple etc.. we need to first understand what data types are accepted on arithmetic functions and also oracle data type internal conversions.

Excerpt from Oracle documentation:

You can use an arithmetic operator with one or two arguments to negate, add, subtract, multiply, and divide numeric values. Some of these operators are also used in datetime and interval arithmetic. The arguments to the operator must resolve to numeric datatypes or to any datatype that can be implicitly converted to a numeric datatype.

Since dates are stored as numbers in the oracle database, we can perform add/subtract arithmetic functions on dates, but multiply/divide are not supported on dates.

When two dates are used in binary +/- operations, the results will be displayed as number of days. If date fields also have time part then result will also have fraction of days (ex 2.33 , 3.44 etc). If we want the result as complete integer value, then we will have to truncate the time part using TRUNC function. The reason for result being number of days (with datatype as integer) is, the dates are stored as days (with data type as number) internally.

Therefore, apart from able to perform +/- on two dates, we can also perform +/- between date and integer (representing number of days).

Important date functions

EXTRACT

  This date function could be used to extract date or time part from fileds with datetime or interval data types

  Ex: select extract(month from sysdate) from dual;

ADD_MONTHS

   This function returns date plus interger months… ex.. add_months(sysdate,1) will return next month date

INTERVAL

   We have both INTERVAL date function and INTERVAL date data type. Both cases, it is a means of dealing with date/time intervals. For example if we want to store year to month interval (ex 2 years 2 months) then we can added column with data type as INTERVAL YEAR [(year_precision)] TO MONTH.

Similarly if we want to just increate the date by 20 seconds then we can use interval function as

sysdate + INTERVAL '20' SECOND

try below query

select to_char(sysdate,'YYYY/MM/DD HH:MI:SS'),to_char(sysdate + INTERVAL '20' SECOND,'YYYY/MM/DD HH:MI:SS') from dual;

MONTHS_BETWEEN

This function returns number of months between two dates

For example select Months_between('02-FEB-15','01-JAN-15') from dual; returns

1.03225806451612903225806451612903225806

The fraction of value return because the difference is 1 month and 1 day .. this one day is converted to month in result.

ROUND and TRUNC

Both round and trunc date functions do same thing.

TRUNC is meant to truncate the time part and ROUND is meant to round the date to nearest day/year or month etc..

For example if the date field has “2016/06/21 07:53:35” then TRUNC function returns 21-JUN-16 but ROUND function return 22-JUN-16.

You can see the difference in these functions if we do not specific the format string and time part in date field is after 12:00 noon. Otherwise both these functions return same result.

We can use ROUND and TRUNC function to return the first day of the year (i.e. using function as TRUNC(DATE,YEAR)) similarly month and week etc..