Date conversion in sql loader control file

If you're trying to load it into a date field and you want to preserve the day of the month, APC's answer is correct. If you're trying to load it into a date field and you want to truncate it to the first day of the month, I think something like this would work:. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams?

Collectives on Stack Overflow. Learn more. Asked 11 years, 5 months ago. Active 11 years, 5 months ago. Viewed 31k times. Improve this question. Alex Larzelere Alex Larzelere 1, 1 1 gold badge 11 11 silver badges 16 16 bronze badges. Add a comment. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Stack Gives Back Safety in numbers: crowdsourcing data on nefarious IP addresses.

Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually. Related 0. Hot Network Questions. Question feed. Hi Tom, I am a Avid fan of your excellent service to Oracle developer community, even though this is my first post. Just asking out of curiosity!!! I hope you have a answer to this question even though it sounds silly ;-! July 12, - am UTC. The format mask has to be interchanged between the first and second records..

Is there a roundabout way of doing that? Anand, July 13, - am UTC. Hi Tom, As i had mentioned earlier it is just a hypothetical situation May be like finding out what is the format in which the date is stored in July 13, - am UTC.

NO, without a format, anything is fair game isn't it. No there "shouldn't" be a way. Tom, i've got a load of data which we've got the 9i external table definitions for, but we need to load this into an 8i database.

Note, the 9i and 8i databases are seperate clients and we have no mechanism for database links etc. January 08, - pm UTC. Susan, May 04, - pm UTC. Tom, We have asc files that have dates in this format: I added mmddyy to your package array. I thought everything was working nicely, but, I noticed that it's transposing certain dates -- for eg. I haven't fully diagnosed it, but, I think the problem occurs when the day begins with a zero.

Any thoughts? Thanks for the help. May 04, - pm UTC. Susan, May 05, - am UTC. Susan, May 05, - pm UTC. May 05, - pm UTC. Thanks Tom! Hi Tom, The above information was very helpful. In my need the coloumn is in timestamp with local time zone. Please guide me how to do that. May 25, - am UTC. Thanks Lamya. June 02, - pm UTC. If so , how to fix it in UNIX? Thanks for your help as aways,.

August 13, - pm UTC. UTF8 to the same as database character set, it succeeded. August 16, - pm UTC. Please correct me if i'm wrong. September 02, - am UTC. Splendid vrd, February 26, - pm UTC. Hi Tom, The discussion is splendid. However, I am facing a rather peculiar problem.

I have a delimited file and have to load its data into 9 different tables, for which I have written 9 different control files. I assure you, I'm using only 10 records as a sample , but stil get these errors.

I don't know what's wrong. February 27, - am UTC. All of the discussions and responses on this issue as well as most others have been helpful but here are 2 very basic questions on SQL Loader. In loading dates: 1. Thank you for a very enjoyable and useful site. March 03, - am UTC. Tom, I have data which contains date in unix timestamp "".

I am trying to load and all of the data being rejected because of the date format. And same data I can load using sqlldr using date 'yyyymmdd hh:mm:ss" in control file but not in external tables. Might be something very small but I don't know what I am missing. Help me out here.