question about to_date()

[jmniu]

Hi, everybody,

I have a question about to_date:

SQL> select to_char( to_date(5373484,'J'),'Jsp') from dual;

TO_CHAR(TO_DATE(5373484,'J'),'JSP')
--------------------------------------------------------------------------
Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four

BUT:
SQL> SELECT TO_CHAR(TO_DATE(5373484,'J'), 'DD-MON-YYYY') sth FROM dual;

STH
-----------
31-DEC-9999

My quesition is to_date(integer, 'J') stands for what? This integer must be between 1 to 5373484.

I got this:
SQL> SELECT TO_CHAR(TO_DATE(1,'J'), 'DD-MON-YYYY') sth FROM dual;

STH
-----------
01-JAN-4712

Could anyone please explain it? Where is 4712 from?
Many thanks.

[jmodic]

'J' stands for Julian date format. Julian calendar (at least the variation Oracle uses) starts counting days at 01-01-4712 years BCE according to our present Gregorian calendar, thats where 4712 in your output comes from.

Check http://otn.oracle.com/docs/products/...0datyp.htm#826

[Shestakov]

DATE - Valid date range from January 1, 4712 BC to December 31, 9999 AD.

The DATE datatype stores point-in-time values (dates and times) in a table. The DATE datatype stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).

Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.

Oracle uses its own internal format to store dates. Date data is stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
------------------
Use of Julian Dates
Julian dates allow continuous dating by the number of days from a common reference. (The reference is 01-01-4712 years BCE, so current dates are somewhere in the 2.4 million range.) A Julian date is nominally a noninteger, the fractional part being a portion of a day. Oracle uses a simplified approach that results in integer values. Julian dates can be calculated and interpreted differently. The calculation method used by Oracle results in a seven-digit number (for dates most often used), such as 2449086 for 08-APR-93.

-------------------------------------------------
in u case :

SELECT TO_CHAR(TO_DATE(1,'J'), 'DD-MON-YYYY') sth FROM dual;

- this is first second of 01-01-4712 years BCE.

[jmniu]

Hi,

Thanks to all of you.