0

Experts,

I have script that loads multiple csv files to DB using SQL Loader. Each csv files is from different systems but the targe table is same for all.

I initially created the all columns are VARCHAR and hence no issues, however the problem started with Date field, where the data format comes different for different system and they are to be loaded to same column.

VerificationDate     '20150501090200
TransDate  '20150902'
CompletedDate '01/01/2018'
ReceivedDate '2015-11-17 12:02:39' or '2018-01-01 12:30:59.213000000'


1) How can i format the three types into any of the one format?
2) Does the column have to be DATE to achieve that?
user2570205
  • 137
  • 1
  • 11
  • To add to the linked answer: yes the table columns should be dates, *never* store dates as strings; and give each column the appropriate format in the control file. – Alex Poole Apr 11 '16 at 17:27
  • @AlexPoole Thanks will try that. – user2570205 Apr 11 '16 at 17:30
  • @AlexPoole can you tell me how to format VerificationDate '20150501090200' since this is times – user2570205 Apr 11 '16 at 17:41
  • [See the documentation](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements004.htm#SQLRF00212). That would be `YYYYMMDDHH24MISS`. – Alex Poole Apr 11 '16 at 17:44
  • @AlexPoole thanks if you notice the data have fraction of seconds, I dont know if DATE field supports that. I probably have to go for TIMESTAMP correct? – user2570205 Apr 11 '16 at 17:54
  • I don't see fractions of seconds in what you've shown. 20150501090200 is 2015-05-10 09:20:00. No decimal fraction. If you did have fractions then you would need a timestamp column, yes, and the equivalent SQL\*Loader format directive. – Alex Poole Apr 11 '16 at 17:58
  • @AlexPoole thanks alex! the original question i asked is to keep everything in same format, so the consumer doestn have to use multiple formatting functions. For e.g If go with format YYYYMMDD then TransDate works but how CompletedDate will work in YYYYMMDD? – user2570205 Apr 11 '16 at 18:04
  • Sorry, I don't know what you mean. It's up to the customer/client how they format the date/timestamp values from the table. How you load the data is irrelevant once it's there. – Alex Poole Apr 11 '16 at 18:07
  • @AlexPoole here is the example of data i receive in fraction . Sometimes I get '2015-11-17 12:02:39' and sometimes 2018-01-01 12:30:59.213000000' how to solve this. – user2570205 Apr 11 '16 at 18:25
  • You haven't shown any data in that format in the question, with or without fractional seconds. Are you getting data for the same column in different formats? That's an issue with whoever is sending you the data. For those two you can use `TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF"`, but if the input format isn't fixed you're likely to have problems at some point. – Alex Poole Apr 11 '16 at 18:33
  • @AlexPoole thats exactly is the problem. Input data comes from multiple systems , which is a legacy thirdparty system. We have to use as it comes, there is no option to change. – user2570205 Apr 11 '16 at 18:43
  • Your question is talking about three separate columns with one format each. It doesn't talk about different systems or different formats for the data bound for the same column. If that is the real issue then you should edit your question to explain your real problem. However, can't you just have a different control file to load data from each source system? – Alex Poole Apr 11 '16 at 18:46
  • @AlexPoole - Well I'm not able to explain so keeping it to technical so that I can try myself something. To your question yes I have created multiple ctrl files for each system. There are 7 systems, each systems sends dates in different format , some send in TIMESTAMP format. I should make sure the DATA TYPE of the column and formatting to be correct so that all 7 Crtl files can handle and load in the same column. – user2570205 Apr 11 '16 at 18:49
  • You can load dates into timestamp columns, but not the other way around (as dates don't allow fractional seconds), so if you might have both make the columns timestamps. – Alex Poole Apr 11 '16 at 19:19
  • @AlexPoole -I have edited my question. – user2570205 Apr 11 '16 at 19:21
  • @AlexPoole - performance of formatting and loading compared to executing format function in sql query, which is better performance? – user2570205 Apr 11 '16 at 20:28

0 Answers0