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
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
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
BR
Yes. If you insert a timestamp into a DATE field, then an implicit conversion takes place. Essentially the subseconds and timezone information is removed.
ASKER
That's great. Best Regards.
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.