Home » RDBMS Server » Server Utilities » sqlldr loading wrong date 12/31/1999
sqlldr loading wrong date 12/31/1999 [message #253790] Tue, 24 July 2007 17:12 Go to next message
raj_23
Messages: 7
Registered: July 2007
Junior Member
LOAD DATA
   TRUNCATE 
   INTO TABLE LICS_STG_FILE 
   (
       RECORD_NO					"LICS_RECORD_NO.NEXTVAL", 	
       MEME_HICN                         	position(1:12),
       CONTRACT_NO		                position(13:17),
       PBP_ID			        position(18:20),
       SEGMENT_NO		                position(21:23),
       RUN_DATE			        position(24:31) "to_timestamp(:RUN_DATE,'YYYYMMDD')",
       SBSDY_EFF_DT		        	position(32:39) "to_timestamp(:SBSDY_EFF_DT,'YYYYMMDD')",
       SBSDY_TERM_DT		        	position(40:47) "NVL2(TRIM(:SBSDY_TERM_DT),to_timestamp(: SBSDY_TERM_DT,'YYYYMMDD'),to_timestamp('12/31/9999','MM/DD/YYYY'))",
       PARTD_SBSDY_PERC		        position(48:50),
       LIS_COPAY_LVL		            	position(51:51),
       ENR_EFF_DT		        	position(52:59) "to_timestamp(:ENR_EFF_DT,'YYYYMMDD')",
       ENR_TERM_DT		                position(60:67) "to_timestamp(:ENR_TERM_DT,'YYYYMMDD')",
       PRTC_PREM		            	position(68:75) "to_number(:PRTC_PREM)",
       PRTD_PREM		            	position(76:83) "to_number(:PRTD_PREM)",
       LATE_PENALTY		    		position(84:91) "to_number(:LATE_PENALTY)",
       MEMD_LICS_SBSDY		        position(92:99) "to_number(:MEMD_LICS_SBSDY)",
       LIS_PENALTY		        	position(100:107) "to_number(:LIS_PENALTY)",
       MEMD_LATE_WAIV_AMT	            	position(108:115) "to_number(:MEMD_LATE_WAIV_AMT)",
       MEMD_TOTAL_PREM        		        position(116:123) "to_number(:MEMD_TOTAL_PREM)" 
   )   


In the file, the dates sometime come as blank (in YYYYMMDD format)..I am substituting a default value of 12/31/9999 when its blank..

Instead of loading 12/31/9999 as a value to the table, for some reason sql loader loads 12/31/1999. Is this a bug ? Or is something wrong in the control file ?
"
ENR_TERM_DT position(60:67) "to_timestamp(:ENR_TERM_DT,'YYYYMMDD')",
"

Thank you very much...
Raj
Re: sqlldr loading wrong date 12/31/1999 [message #253814 is a reply to message #253790] Tue, 24 July 2007 23:14 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Could it not be that some records actually have a date of 19991231?
Re: sqlldr loading wrong date 12/31/1999 [message #253830 is a reply to message #253790] Wed, 25 July 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, it is a very bad idea to have a date like 31/12/9999.
This fools the optimizer.
Why don't you keep NULL?

Regards
Michel
Re: sqlldr loading wrong date 12/31/1999 [message #253975 is a reply to message #253830] Wed, 25 July 2007 07:18 Go to previous messageGo to next message
raj_23
Messages: 7
Registered: July 2007
Junior Member
The file has spaces correctly. Its not wrong in the input...

SQLLoader is changing 12/31/9999 to 12/31/1999..

12/31/9999 is the high date used in the app (not my decision) ..But I wonder why does SQLLoader does this.. Is this a bug ?
Re: sqlldr loading wrong date 12/31/1999 [message #253981 is a reply to message #253975] Wed, 25 July 2007 07:24 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is this a bug ?

Yes it is a bug in your application.
(I hate this question).

Regards
Michel
Previous Topic: Export time problem (merged)
Next Topic: While Export one of our DB I've got the error
Goto Forum:
  


Current Time: Sat Dec 03 06:05:57 CST 2016

Total time taken to generate the page: 0.09754 seconds