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

Oracle: Benefits of Packages over standalone functions and procedures (Procedures vs packages)

Oracle: Benefits of Packages over standalone functions and procedures (Procedures vs packages)
Most us know oracle packages as convenient way of grouping functions and procedures .i.e. having all at one place. There oracle packages offer more than this

Overloading: We have multiple functions/procedures with same name..  during the execution, based on number of parameters and data types will determine the correct version to be invoked.
This way, every time we need additional functionality on top of existing, we do not need to modify existing functions/procedures.. we can simply create new without impacting existing applications...

Scope of Variable :  We can declare a variable in package specification .. i.e. global variable... to have the variable accessible during the session in order to pass info between the packages/procedures/functions during a session. or declare with in body to be private to the package to be accessible only by the package, not to be accessible to other packages in the same session.

Global and Private variables are persistent between multiple calls of package during the session.. but if want it to reset for every call then we need have PRAGMA SERIALLY_REUSABLE; in both specification and body.. Note: PRAGMA is compiler directive .

Initialization: Package Body can have BEGIN and END; (not a function or procedure)  block. This is executed only once , very first time any one of procedure or function referenced from package and information retrieved/set by this pl/sql block will be available for rest of the session.

Information Hiding: Only the package specification is visible to people who have execute privilege on the package. We can also control only specific procedures or functions are accessible by creating other procedures calling specific package procedures.. Therefore packages provide very good encapsulation technique.

Insulation from dependency crisis:   If we are creating standalone procedures and are interlinked (called one from other).. a change in one procedure will invalidate all the parent procedures referencing changed procedure. Though oracle will automatically recompile when the invalid procedure called, it will be costly in terms of resources and performance. This problem is not there with packages as any parent procedure/function calling one of package procedure/function is only dependent on package specification and do not require recompilation when package body change.

Note: All the dependencies are present in the system table user_dependencies

Enabling recursion: In the standalone procedures or functions, the recursion is not allowed example.. have to procedures X, Y... calling Y from X and also calling X from Y is not allowed.. the procedures would not compile at all.. but if same procedures are created in Packages then its allowed.

When Not to use packages

Packages are not good fit to create functions for function-based index. Because of isolation from dependency chain, the function body changes will not disable or enforce rebuild for function-based index which could yield wrong results. Therefore its always better to use standalone function for function-based index.