question about to_date()
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: question about to_date()

  1. #1
    Join Date
    Jan 2002
    Posts
    33
    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.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    '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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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.

  4. #4
    Join Date
    Jan 2002
    Posts
    33

    I got it

    Hi,

    Thanks to all of you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width