Date Range in Oracle – Minimum Possible Date and Maximum Possible Date

By | August 22, 2015

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?

WOW! Did you like this post? We'll send more interesting posts like Date Range in Oracle – Minimum Possible Date and Maximum Possible Date to you!
Enter your Email Address: