Home > IT Answers > AS/400 > SQL0181 error when sending PF from iSeries to PC
Barbarafj
0 pts.
 SQL0181 error when sending PF from iSeries to PC
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??

Software/Hardware used:
ASKED: March 12, 2007  2:55 PM
UPDATED: January 6, 2010  4:15 PM
RELATED QUESTIONS
Answer Wiki:
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
Last Wiki Answer Submitted:  January 3, 2010  12:24 pm  by  WaltZ400   645 pts.
All Answer Wiki Contributors:  WaltZ400   645 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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

DoneThat
 830 pts.