Avatar of GurcanK
GurcanK
 asked on

How to insert some date types with SQLLoader?

Dear Experts,

I have some files with different kinds of data and each file type has different date-time field. I want to load those files with SQLLoader to Oracle tables. In each related Oracle table, there is a field with Date type. However, in the files, representation of date-time fields differs according to file type.  For example one file type has a date-time field with data sample: "2014-11-10 01:07:57". I'will use YYYY-MM-DD HH24:MI:SS for this in control file. Unfortunately not all file types  are easy like this. (See Below) Could you please help for the following?

2014-05-09 12:14:08+02:00DST
2012-01-16 09:24:43.0
2014-11-09 04:00:00 AM
2014-11-8-16-14-55

Thanks in Advance.

BR
Oracle Database

Avatar of undefined
Last Comment
GurcanK

8/22/2022 - Mon
johnsone

Not sure I completely understand what you are asking.

Are you asking how to specify the format within the SQL*Loader control file?  That is documented here -> https://docs.oracle.com/cd/E11882_01/server.112/e22490/ldr_field_list.htm#i1016161

Or, are you asking how you can change the mask without changing the control file?  My guess on that (which I have not tested) is that you could set the NLS_DATE_FORMAT in the environment.  I believe that the default format is picked up from there, the documentation doesn't mention the default that I could see.
GurcanK

ASKER
I actually just asked how I can represent these in TO_DATE function such as TO_DATE(VALUE, 'YYYY-MM-DD HH24:MI:SS')

2014-05-09 12:14:08+02:00DST
 2012-01-16 09:24:43.0
 2014-11-09 04:00:00 AM
 2014-11-8-16-14-55
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
GurcanK

ASKER
Thanks, and one last point: Do you mean if I insert those TimeStamp fields (first two of above) to Date field using SQLLOADER then otomatic conversion occurs?

BR
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
johnsone

Yes.  If you insert a timestamp into a DATE field, then an implicit conversion takes place.  Essentially the subseconds and timezone information is removed.
GurcanK

ASKER
That's great. Best Regards.