Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Terry.

Asked: March 23, 2017 - 2:45 pm UTC

Last updated: April 08, 2017 - 5:22 am UTC

Version: 10.2.0.4

Viewed 1000+ times

You Asked

Hi,

I've been tasked with loading several csv data files into a single oracle database, which would be no issue but for the fact that I have no less than 7 different date/time format to deal with. Listed below are the 5 that I don't know how to handle:

18-Sep_2014 12:39:31 GMT
2015-02-11T19:12:02Z
2015-02-11T19:12:02.901Z
2015-02-11 19:12:02 UTC
Tue Dec 09 15:22:12 GMT 2014

I tried every trick I know of with regards to use TO_DATE formats to get the odd ones in and have had no luck.

So my question is, Are there formats that can handle the 5 formats listed above?

Thanks for any assistance.

Terry


and Connor said...

You can have a little plsql function that will cycle through known formats, eg

SQL> create or replace
  2  function to_date_custom(p_str varchar2) return date is
  3    l_formats sys.odcivarchar2list :=
  4      sys.odcivarchar2list(
  5        'dd/mm',
  6        'yyyy-mm-dd',
  7        'hh24:mi',
  8        'dd-mm-yyyy-hh24');
  9    l_date date;
 10  begin
 11    for i in 1 .. l_formats.count loop
 12      begin
 13        l_date := to_date(p_str,l_formats(i));
 14        return l_date;
 15      exception
 16        when others then
 17          if i = l_formats.count then return null; end if;
 18      end;
 19    end loop;
 20  end;
 21  /

Function created.

SQL>
SQL> select to_date_custom('01/02') from dual;

TO_DATE_C
---------
01-FEB-17

1 row selected.

SQL> select to_date_custom('2017-01-01') from dual;

TO_DATE_C
---------
01-JAN-17

1 row selected.

SQL> select to_date_custom('01-03-2016-12') from dual;

TO_DATE_C
---------
01-MAR-16

1 row selected.

SQL> select to_date_custom('01/02-2016 12:34') from dual;

TO_DATE_C
---------


1 row selected.

SQL> select to_date_custom('01/02/2016') from dual;

TO_DATE_C
---------
01-FEB-16

1 row selected.


You might be wondering why the last one worked, even though we didnt have an explcit format mask for it. There are some standard masks we will always try.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Cool Piece of Code

Terry Jensen, March 24, 2017 - 1:16 pm UTC

Connor,
That is a cool piece of code and I will save it for use later but how can I use that in a SQLLDR command file.
Connor McDonald
March 25, 2017 - 3:25 am UTC

Just like any other function in SQLLDR, eg

fields terminated by ','
(       x ,
        y ,
        z "to_data_custom(:y)"
)


or similar. Search this for FILLER and BOUNDFILLER for more examples

Terry Jensen, March 29, 2017 - 3:37 pm UTC

Connor,

This code works great if you have no imbedded characters like GMT or UDT or a T inserted between the date and time values. What is the best way to handle this in the procedure?

Terry
Connor McDonald
March 30, 2017 - 12:37 am UTC

As long as elements map to valid format mask strings in Oracle

http://docs.oracle.com/database/122/SQLRF/Format-Models.htm#SQLRF00212

then you can cycle through as many variations of formats as you want. You simply just keep extending the 'l_formats' array.


Terry Jensen, April 06, 2017 - 4:19 pm UTC

I created the proc as you suggested and when I try to use it in my loader control file I am still getting an error:

Expected "," or ")" , found "CUSTOM_TO_DATE"

Here is what I have in my control file.

fields terminated by ','
trailing nullcols
(
name char(500) enclosed by '"',
createDate "CUSTOM_TO_DATE('xx','yy', :createDate)" enclosed by '"'
)

I hope you can show me the error of my ways.

Terry

Fixed

Terry Jensen, April 07, 2017 - 2:12 pm UTC

I'm not exactly sure what I had wrong but here is what I did to fix it.

I moved the ENCLOSED BY '"' up to the line that says FIELDS TERMINATED BY ','

and then removed it from all the individual lines. I think there must have been a quote in the wrong place.

Any thanks for all your help.

Terry
Connor McDonald
April 08, 2017 - 5:22 am UTC

Thanks for getting back to us, and saving us some time.


More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library