Change Excel date number to Oracle date

16,702

Solution 1

The value you have is the number of days since the 30th of December 1899. Try:

select to_char(
  to_date('1899-12-30', 'YYYY-MM-DD') + 41293,
  'DD/MON/YYYY') from dual

Solution 2

Quoting from Oracle forum:

You need a tool to do that, since format is to tell oracle what type of format you have on your date type in the spreadsheet. While you may not have opted to format the date in Excel, it will appear as a date in the previewer. Use the format from this as a guide to enter into the datatype panel.

so, if you have a date that looks like this in the previewer, 19-jan-2006, then your format for the data type panel if you choose to insert that column is going to be DD-MON-YYYY,

Option 1:

Try using the below functions

FUNCTION FROMEXCELDATETIME ( ACELLVALUE IN VARCHAR2 )
    RETURN TIMESTAMP
IS
    EXCEL_BASE_DATE_TIME CONSTANT TIMESTAMP
            := TO_TIMESTAMP ( '12/31/1899',
                           'mm/dd/yyyy' ) ;

    VAL CONSTANT NUMBER
            := TO_NUMBER ( NULLIF ( TRIM ( ACELLVALUE ),
                                '0' ) ) ;
BEGIN
    RETURN EXCEL_BASE_DATE_TIME
          + NUMTODSINTERVAL ( VAL
                          - CASE
                                WHEN VAL >= 60
                                THEN
                                    1
                                ELSE
                                    0
                            END,
                          'DAY' );
END;

FUNCTION TOEXCELDATETIME ( ATIMESTAMP IN TIMESTAMP )
    RETURN VARCHAR2
IS
    EXCEL_BASE_DATE_TIME CONSTANT TIMESTAMP
            := TO_TIMESTAMP ( '12/31/1899',
                           'mm/dd/yyyy' ) ;

    DIF CONSTANT INTERVAL DAY ( 9 ) TO SECOND ( 9 )
            := ATIMESTAMP
               - EXCEL_BASE_DATE_TIME ;
    DAYS CONSTANT INTEGER := EXTRACT ( DAY FROM DIF );
BEGIN
    RETURN CASE
              WHEN DIF IS NULL
              THEN
                  ''
              ELSE
                  TO_CHAR (  DAYS
                         + CASE
                               WHEN DAYS >= 60
                               THEN
                                   1
                               ELSE
                                   0
                           END
                         + ROUND ( ( EXTRACT ( HOUR FROM DIF )
                                  + ( EXTRACT ( MINUTE FROM DIF )
                                    + EXTRACT ( SECOND FROM DIF )
                                      / 60 )
                                    / 60 )
                                 / 24,
                                 4 ) )
          END;
END;

Option 2:

The excel function would be =TEXT(B2,"MM/DD/YY"), to convert an Excel date value stored in B2. Then try using the test character in Oracle

If considering 1900 Jan 1st as start date,

SELECT
      TO_CHAR ( TO_DATE ( '1900-01-01',
                      'YYYY-MM-DD' )
              + 41293,
              'DD/MON/YYYY' )
FROM
      DUAL
Share:
16,702

Related videos on Youtube

Sagar Tippe
Author by

Sagar Tippe

Updated on October 10, 2022

Comments

  • Sagar Tippe
    Sagar Tippe about 1 year

    I'm having date as 41293 in oracle, how can i show it in DD/MON/YYYY format?

    If i copy pasted it in Excel and change it to date format, it shows 01/19/13 Please help me.

  • Sagar Tippe
    Sagar Tippe about 10 years
    when i execute this SQL, the answer is 19/JAN/2013, but in data given the value is 21-JAN-13. Is the client data is wrong or this is because any other factors?
  • Sagar Tippe
    Sagar Tippe about 10 years
    they are using this 41293 as a period_ID in their data warehouse. and the other attributes for that table are Calendar date:21-JAN-13, Day_Name: Monday, etc. please let me know the SQL which will convert 41293 to 21-JAN-13
  • Frank Schmitt
    Frank Schmitt about 10 years
    In your question you wrote "it shows 01/19/13", which is returned by my SQL statement. But now you want the 21st of January ?
  • Sagar Tippe
    Sagar Tippe about 10 years
    i checked the client data just now. sorry for that, but this is what the data i got.. I think, the data i got from client is wrong.. Thanks Frank.
  • MT0
    MT0 about 4 years
    "Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900." from Microsoft's documentation. Should this be SELECT DATE '1899-12-31' + 41293 FROM DUAL (as using the 30th December is one day out)?
  • MT0
    MT0 about 4 years
    "Note: Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900." from Microsoft's documentation. Should this be SELECT DATE '1899-12-31' + 41293 FROM DUAL (as using the 1st January is one day out)?
  • Frank Schmitt
    Frank Schmitt about 4 years
    @MT0 Interestingly, my version (using 1899-12-30) returns the wrong result for Jan 1st 1900, but the correct result for Jan 1st 2008 as opposed to the version using 1899-12-31 (I used the numbers from the link you provided: select to_date('1899-12-30', 'YYYY-MM-DD') + 39448 from dual It seems like somewhere between 1900 and 2008, either Oracle or Excel forgot one day or invented one :O
  • MT0
    MT0 about 4 years
    @FrankSchmitt Excel thinks that 60 is 1900-02-29; however, 1900 was not a leap year so every day after that is 1 day out.