Oracle SQL - Importing Dates from CSV (Excel Format)

Question

Sorry for the noob question (I'm trying to practice SQL with some databases), but how can I convert the following Excel date formats to MM-DD-YYYY?

The original Excel column is in American format 'MM-DD-YYYY' and it goes to SQL as VARCHAR2:

So, as far as I know, I need to convert it to a number and then use 'TO_DATE()' function (just got the first 50 rows to test):

     SELECT TO_DATE(TO_NUMBER(DATE1), 'MM-DD-YYYY')  FROM (
         SELECT * FROM CHEMICALS2 FETCH FIRST 50 ROWS ONLY
     )

and got the error '01843.0000 - Not a valid month'

I also tried to add 41293, after converting to number but got the same error.

Do you have a better idea to format that instead of changing the original csv file?

Stop. Alter your table. Change the column data type to DATE. — thatjeffsmith
– thatjeffsmith, Commented Oct 24, 2018 at 10:18

Giovanni Esposito · Accepted Answer · 2018-10-26 17:13:44Z

1

Try removing TO_NUMBER function.

An example,

SELECT TO_DATE(DATE1, 'MM-DD-YYYY')  FROM (
    SELECT '10-18-2018' as date1 from dual
);

--UPDATE --Important note: 37894 it's the number from the excel cell

select (to_date('01011900','DDMMYYYY')-2)+37894 from dual;

Please: check if returns the correct date.

Good luck

edited Oct 26, 2018 at 17:13

answered Oct 24, 2018 at 22:23

Giovanni Esposito

741 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Petter_M Over a year ago

got the same ORA-01843: not a valid month error. The dates to be transformed are in Excel's format, such as 43070, 43344, 43282, but even applying some transforming functions (in other forums), I got the same problem...

Giovanni Esposito Over a year ago

mmm... Ok, according with the next link community.oracle.com/thread/205184?start=0&tstart=0 the excel date number is an internal representation. I've modified my answer to put their recommendation. Please, let me know if it works fine

Petter_M Over a year ago

Got it! Thank you very much! I put the following:

SELECT (to_date('01011900','DDMMYYYY')-1) + A.DATE1 AS "DATE2" FROM (     SELECT DISTINCT DATE1 FROM CHEMICALS2 FETCH FIRST 50 ROWS ONLY   ) A

Collectives™ on Stack Overflow

Oracle SQL - Importing Dates from CSV (Excel Format)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related