Xtykutl

I was trying to covert same Excel date format to dd/mm/yyyy format. But I am not getting the correct result.

43236   16-May-18

43249   29-May-18

43238   18-May-18

43228   8-May-18

43238   18-May-18

I am using the below query


SELECT to_date('31-12-1899','dd-mm-yyyy')+43236 FROM dual
UNION ALL
SELECT to_date('31-12-1899','dd-mm-yyyy')+43249 FROM dual
UNION ALL
SELECT to_date('31-12-1899','dd-mm-yyyy')+43238 FROM dual
UNION ALL
SELECT to_date('31-12-1899','dd-mm-yyyy')+43228 FROM dual
UNION ALL
SELECT to_date('31-12-1899','dd-mm-yyyy')+43238 FROM dual;


I am getting the below result which is not correct

17-MAY-18

30-MAY-18

19-MAY-18

09-MAY-18

19-MAY-18

Please help me on that. I am using Oracle 11g R2.

You're simply off by 1. Use 30 Dec 1899 (instead of 31 Dec 1899) as the reference date for index 0.

SELECT to_date('31-12-1899','dd-mm-yyyy') + 43236 FROM dual

The reason it's 30 Dec 1899 and not 1 Jan 1900 is twofold:

1. In Excel, 1 Jan 1900 is saved as 1 (and not as 0 as an IT guy would expect).


2. Excel treats the year 1900 has a leap year even though it wasn't. Therefore, Excel also has a number for 29 Feb 1900, which did not exist.

If I understand correctly you can try to use TO_DATE and set language culture then TO_CHAR for your expected format.

select TO_CHAR(TO_DATE('16-May-18','DD-Mon-YY', 'nls_date_language = american'),'DD/mm/yyyy')

from dual

or like this.

SELECT TO_CHAR(to_date('31-12-1899','dd-mm-yyyy')+43236,'DD/mm/yyyy') FROM dual

sqlfiddle

You can try below using to_char() function

select to_char(TO_date('31-12-1899', 'dd-mm-yyyy')+43236,'DD/MM/YYYY')  from dual