Like any other data type in oracle, even DATE data type has a length restriction. A Date data type can hold a maximum data of 7 bytes. You can refer to this article from Oracle corporation on Internal Oracle Database Data Types and their limits which shows the limit restrictions of different data types.
Below is the history of DATE data type range in glance:
| Oracle Version | Date Data type Range |
|---|---|
| Oracle 7 | from January 1, 4712 BC to December 31, 4712 AD |
| Oracle 8 | from January 1, 4712 BC to December 31, 9999 AD |
| Oracle 9 | from January 1, 4712 BC to December 31, 9999 AD |
| PL/SQL | from January 1, 4712 BC to December 31, 9999 AD |
Note: You can observe data range has been increased from Oracle database 8 version.
Oracle database internally stores date in Julian calendar format, Julian dates (abbreviated JD) are simply a continuous count of days and fractions since noon Universal Time on January 1, 4713 BCE (on the Julian calendar). For example, today i.e, 23-AUG-2015 is the 2457258th day from 01-JAN-4713 BC of Julian calendar.
Since oracle stores date in integer format, we can comfortably do arithmetic calculations like addition and subtraction on date value.
SQL Query to find Julian day – number of days that have passed since January 1, 4713 B.C
SELECT TO_CHAR(SYSDATE, 'J') FROM dual; TO_CHAR ------- 2457258 |
As of today, in HRMS perspective oracle considers 31-DEC-4712 as maximum possible date, any date beyond this is out of scope and is not acceptable.
It is suggestible to use below functions provided by oracle to find minimum and maximum possible dates instead of just hardcoding date, because oracle may increase the range in future version.
HR_GENERAL.END_OF_TIME
HR_GENERAL.START_OF_TIME
We may think what will happen if we reach 4712 year. I guess oracle will increase the date range by that time, but do you think mother earth will exist till that time with the way we pollute our environment?