Wednesday, April 6, 2011

How do you obtain the maximum possible date in Oracle?

Is there a function built into Oracle that will return the highest possible date that may be inserted into a date field?

From stackoverflow
  • SELECT  TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss')
    FROM    dual
    

    Note that minimal date is much more simple:

    SELECT  TO_DATE(1, 'J')
    FROM    dual
    
  • I do not know of a function but according to this article:

    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.

  • From the 11g docs:

    Oracle Database can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or 'AD').

    http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/datatype.htm#i1847

    Thilo : Common Era... Is that a politically correct term for the "confessionally biased" AD ?
  • Another ways, just for fun:

    SELECT to_date(5373484, 'J')
      FROM dual;
    
    SELECT date '9999-12-31' + (1 - 1/24/60/60) 
      FROM dual;
    

0 comments:

Post a Comment