Convert Oracle Date Format to Excel Date Format

MS Excel uses a numeric format, called Serial Date format, when storing and dealing with dates and times.   Serial date is calculated by taking a given date and determining the number of days that have passed since that date and 01-Jan-1900 (actually 00-Jan1900, but that’s a story for another time).  Why is this important to an Oracle developer?  If you are using a tool such as the ExcelDocumentType to generate an Excel document with PL/SQL and you are trying to apply an Excel style to an Oracle Date data type … it will not work.  The Oracle Date column must be converted to serial format before Excel will recognize it as a date and apply any kind of special date formatting to it.  Luckily, the conversion formula is simple.  The following function will take an Oracle Date data type and convert it to an Excel Date data type (or serial 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;
/

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Nov 23 22:31:54 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> select convertOracleToSerialDate(’05-MAY-1968′) from dual;

CONVERTORACLETOSERIALDATE(’05-MAY-1968′)
—————————————-
24963

If we pass in the value ’05-MAY-1968′ to the function above, we will get the value 24963.  Now, if we place this value in an Excel Spreadsheet and format the cell as a Date type, it will be converted to ‘5/5/1968′ (or what ever date format you choose for that cell or cells).

The reverse is also possible.  If we have a Serial Date value, and we want to convert it to an Oracle Date type, we would use a function such as:

CREATE OR REPLACE FUNCTION convertSerialToOracleDate(p_serial_date NUMBER) RETURN DATE
IS

v_orcl_date DATE := NULL;

BEGIN

v_orcl_date := (((TO_DATE(’01-JAN-1900′,’DD-MON-YYYY’)) + p_serial_date-1));

IF TRUNC(v_orcl_date) > TO_DATE(’01-MAR-1900′,’DD-MON-YYYY’) THEN

v_orcl_date := v_orcl_date – 1;

END IF;

RETURN v_orcl_date;

END;
/

SQL*Plus: Release 10.2.0.4.0 – Production on Mon Nov 23 22:45:44 2009

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production

SQL> select convertSerialToOracleDate(24963) from dual;

CONVERTSE
———
05-MAY-68

SQL>

If we take the result we obtained from the first function, 24963, and apply the second formula (using SQL*Plus, SQL Developer, TOAD, etc), we get …05-May-68.

Comments are closed.