Re: Invalid dates in data transfer

[CRPence <CRPbottle@xxxxxxxxx>]

James Rich wrote:
> We're trying to use data transfer in iSeries Access to transfer a
> file from the i to the PC.  The file includes date fields.  Some
> of the dates are *LOVAL and when transferring we get error
> SQL0181 "Value in date, time, or timestamp field not valid".  Any
> solution to getting the file transferred?

  By default the database will retrieve date data from a table 
using the "date format" for the job.  The application would need to 
override that default to ask the database to use a four-digit-year 
date format; i.e. /job/ date formats support only two-digit for the 
year.  As such, if there are transfer options to format dates or 
cast dates to character strings, then ensure such an option 
indicates to use a four-digit-year format.  If the transfer defines 
the actual SQL SELECT, then use the SQL scalar CHAR function to cast 
from date to character string using a chosen date format [e.g. 
CHAR(fld,ISO)], or use CASE to replace just the *LOVAL to either the 
NULL value or to a date within the 100 year window of valid 
two-digit-year dates.

  Or perhaps the error is for lack of a PTF [the following are 
found searching SQL0181]:

V5R3 iSeries Access client code in 5722XE1:
http://www-933.ibm.com/eserver/support/fixes/fixcentral/fixdetails?fixid=SI28055

V5R4 iSeries Access client code in 5722XE1:
http://www-933.ibm.com/eserver/support/fixes/fixcentral/fixdetails?fixid=SI27741

  Instead of specifying CAST or CASE directly in the SQL SELECT for 
the transfer, that could be accomplished instead by encapsulating 
the SELECT in a VIEW; i.e. by creating a VIEW [that casts the date 
to a character string representation with a four digit year format, 
or replaces the invalid values], which is then transferred instead.

Regards, Chuck