Change Excel date number to Oracle date

Question

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.

Answer 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

Answer 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