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