SQL0181 error when sending PF from iSeries to PC

pts.
Tags:
AS/400
When I try to send my PF from iSeries to the PC, I get SQL0181 error because the dates in the file (with data type L) are not between 01/01/1940 and 12/31/2039. The error msg says that dates are valid between 01/01/0001 and 12/31/9999, but I have narrowed the error down to those dates that fall outside of the range of 01/01/1940 thru 12/31/2039. How can I get my 0001 and 9999 years to transfer successfully??

Answer Wiki

Thanks. We'll let you know when a new response is added.

If you look at the text for the SQL message, 0001 through 9999 are valid year ranges. Are the dates correct in the from file? Do you have a date 31 in a month where only 30 exist? Do you have incorrect leap year dates? Remember there is a Feb 29th in all years that are divisible by 4 but if the year ends in 00 it must be evenly divisible by 40 to be a leap year.
You might want to create a quickie RPG program to run some TEST operations on your date fields to see if they are valid. If you have any of the above errors this will point them out.

==================================================================

How are you “transferring” the file? Have you set the connection to recognize the date format? Clearly, this has nothing to do with “date 31 in a month where only 30 exist” nor any of the other listed possibilities.

This is apparently because your PC is using the 100-year ‘window’ for valid years.

Examine the settings for the connection. If this is ODBC or similar, the connection attributes need to allow for the same settings as the database allows.

Tom

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • DoneThat
    Perhaps this SQL routine I use to fix bad dates would help? The dates in our legacy system are all CC YY MM DD. I use it to set all invalid dates to 1/1/1950. You can easily adapt. FYI: I'm not the original author. Lost the proper attribution.
    UPDATE	r091128pe.cusmas AS UCM
    
    SET	UCM.cm_chg_cn  =  19,
    	UCM.cm_chg_yr  =  50,
    	UCM.cm_chg_mo  =  01,
    	UCM.cm_chg_da  =  01
    
    WHERE	
    /*  Any TRUE statement is an INvalid date.  */
    	(	UCM.cm_chg_cn  NOT IN  ( 19, 20 )
    			OR
    		UCM.cm_chg_cn  =  19  AND  UCM.cm_chg_yr  NOT BETWEEN  50 AND 99
    			OR
    		UCM.cm_chg_cn  =  20  AND  UCM.cm_chg_yr  NOT BETWEEN  00 AND 49
    			OR
    		UCM.cm_chg_mo  NOT BETWEEN  01 AND 12
    			OR
    		UCM.cm_chg_mo  IN  ( 1, 3, 5, 7, 8, 10, 12 )  AND  UCM.cm_chg_da
    							NOT BETWEEN  01 AND 31
    			OR
    		UCM.cm_chg_mo  IN  ( 4, 6, 9, 11 )  AND  UCM.cm_chg_da
    							NOT BETWEEN  01 AND 30
    			OR
    		UCM.cm_chg_mo  =  2  AND  UCM.cm_chg_mo  NOT BETWEEN  01 AND 28
    			OR
    		UCM.cm_chg_mo  =  2  AND  UCM.cm_chg_yr  =  00  AND  UCM.cm_chg_mo
    							NOT BETWEEN  01 AND 28
    			OR
    		UCM.cm_chg_mo  =  2  AND  UCM.cm_chg_yr  IN  ( 4, 12, 16, 20,
    				24, 28, 32, 36, 40, 44, 48, 52, 56, 60, 64,
    				68, 72, 76, 80, 84, 88, 92, 96 )  AND  UCM.cm_chg_mo
    							NOT BETWEEN  01 AND 29
    	)
    
    WITH	NC;
    
    Gary
    830 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following