1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to CONVERT NUMBER to DATE with NUmber series of 1198846200000

Discussion in 'SQL PL/SQL' started by rancer, Apr 12, 2012.

  1. rancer

    rancer Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    Can Anyone help me to find ways on how to CONVERT this NUMBER '1198846200000' to a DATE format. The expected date is 29-MAR-12. I have the idea on how to convert the number series in EXCEL into Date format but this 13 characters Number Datatype is big question mark. I don't know the logical transformation on how they came up with this number series out of date.

    Sample Excel convertion of number series to date:

    CREATE OR REPLACE FUNCTION CONVERTORACLETOSERIALDATE(p_date DATE) RETURN NUMBER
    IS
    v_date_differential NUMBER(1) := 0;
    BEGIN
    IF TRUNC(p_date) >= TO_DATE('01-MAR-1900','DD-MON-YYYY') THEN
    v_date_differential := 1;
    END IF;
    RETURN ((trunc(p_date) + v_date_differential) - (TO_DATE('01-JAN-1900','DD-MON-YYYY')- 1));
    END;

    TO QUERY:
    SELECT CONVERTORACLETOSERIALDATE('29-MAR-2012') from DUAL;

    RESULT: 40997

    ANY HELP IS HIGHLY APPRECIATED.

    THANKS
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Where is this suspect number coming from? Converting the desired date to a UNIX time_t value I get:

    Code (SQL):
     
    SQL> SELECT (to_date('29-MAR-2012','DD-MON-YYYY') - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400) AS dt FROM dual;
            DT
    ----------
    1332979200
     
     
    Converting this to the number of seconds elapsed since 01-Jan-1900:

    Code (SQL):
     
    SQL> SELECT (to_date('29-MAR-2012','DD-MON-YYYY') - to_date('01-JAN-1900','DD-MON-YYYY')) * (86400) AS dt FROM dual;
            DT
    ----------
    3541968000
     
    Neither of thse values comes even remotely close to this value you've posted.

    Post which utility generated this number for 29-MAR-2012.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    David it right. Unless Po have any clue how it is generated, nobody can do th reverse engineering.