?
Solved

How to insert some date types with SQLLoader?

Posted on 2014-11-10
6
Medium Priority
?
702 Views
Last Modified: 2014-11-10
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
0
Comment
Question by:GurcanK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 35

Expert Comment

by:johnsone
ID: 40432455
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.
0
 

Author Comment

by:GurcanK
ID: 40432465
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
0
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 40432482
The first two are timestamps, not dates, so that would be:

to_timestamp('2014-05-09 12:14:08+02:00DST', 'yyyy-mm-dd hh24:mi:sstxh:tzmtzd')
to_timestamp('2012-01-16 09:24:43.0', 'yyyy-mm-dd hh24:mi:ss.ff1')

If you are inserting into a field with a DATE datatype, the conversion should be done for you automatically.

The rest are:

to_date('2014-11-09 04:00:00 AM','yyyy-mm-dd hh:mi:ss AM')
to_date('2014-11-8-16-14-55','yyyy-mm-dd-hh24-mi-ss')


Documentation for DATE format models -> https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:GurcanK
ID: 40432486
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
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40432493
Yes.  If you insert a timestamp into a DATE field, then an implicit conversion takes place.  Essentially the subseconds and timezone information is removed.
0
 

Author Comment

by:GurcanK
ID: 40432495
That's great. Best Regards.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question