How do you obtain the maximum possible date in Oracle?

Question

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

Why ? If you start putting in dummy 'high-date' values into a table, it can 'confuse' the optimizer which will assume that you have a whole range of date values spread over the next 8 thousand years. — Gary Myers, Mar 27 '09 at 09:30

Quassnoi · Accepted Answer · 2015-02-13T15:56:25.697

36

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

edited Feb 13 '15 at 15:56

answered Mar 26 '09 at 20:57

Quassnoi

413,100
91
616
614

14

I think for most people's purposes `TO_DATE('9999', 'yyyy')` would be a much shorter and acceptable alternative, especially when using to coalesce nulls in comparisons. – Alex Apr 17 '12 at 16:18
2

@Alex: Yes, but the op asked for "maximum possible date". – Quassnoi Apr 17 '12 at 16:57
1

I know. Just adding my 2 cents in hope it helps someone. – Alex Apr 17 '12 at 18:33
2

`TO_DATE(1, 'J')` returns 1st of January, the current year for me? – Richard Dingwall Apr 20 '12 at 12:56
@RichardDingwall: for everyone else, it does not. http://www.sqlfiddle.com/#!4/8ce59/2 – Quassnoi Apr 20 '12 at 12:58
2

TO_DATE(1, 'J') returned January, 01 4713 00:00:00-0800 for me in sqlfiddle – Partha Choudhury Aug 16 '12 at 20:59
1

`SELECT to_char(TO_DATE('31.12.9999 23:59:59', 'dd.mm.yyyy hh24:mi:ss'), 'J') FROM dual;` would give `5373484`. Hence the second query should be `SELECT to_date(5373484, 'J') FROM dual;`. – Lalit Kumar B Feb 13 '15 at 05:13
@LalitKumarB: have you by any chance noticed it says "miminal date"? – Quassnoi Feb 13 '15 at 15:56
@Quassnoi, completely missed it, apologies. I need a coffee break. I never expected a completely opposite solution. Question is about maximum date value, so why did you provide an opposite solution? Jusr curious. – Lalit Kumar B Feb 13 '15 at 16:05
@LalitKumarB: no probs. I provided both, just in case. – Quassnoi Feb 13 '15 at 16:14

score 8 · Answer 2 · answered Mar 26 '09 at 21:02

8

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

answered Mar 26 '09 at 21:02

Angelo Marcotullio

801
6
6

4

Common Era... Is that a politically correct term for the "confessionally biased" AD ? – Thilo Mar 27 '09 at 08:37
Real answer to the question... Jan 1 4712 till Dec 31 9999 – Doogle Apr 19 '16 at 15:43

score 2 · Answer 3 · answered Mar 26 '09 at 20:58

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.

score 2 · Answer 4 · edited Feb 13 '15 at 05:19

2

Another ways, just for fun:

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

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

edited Feb 13 '15 at 05:19

Lalit Kumar B

47,486
13
97
124

answered Mar 26 '09 at 22:50

FerranB

35,683
18
66
85

The first query should be `SELECT to_date(5373484, 'J') + (1 - 1/24/60/60) FROM dual;` – Lalit Kumar B Feb 13 '15 at 05:18

score 0 · Answer 5 · answered Mar 02 '12 at 22:02

0

In Julian format (JDDD) the max Oracle date is 2086307365.

answered Mar 02 '12 at 22:02

TonyP

5,655
13
60
94

How do you obtain the maximum possible date in Oracle?

5 Answers5

Linked