Re: Invalid dates in data transfer

[rob@xxxxxxxxx]

How does System i Access handle null dates?  Should you be using those 
instead?

System i Access does have a sql screen you can modify.  I just ran one 
with this in the select box
case mydate when '0001-01-01' then '1970-01-01' else mydate end
I ended up receiving 
"06/20/09"
"01/01/70"
from these file values
> CREATE TABLE ROB/DELETEME (MYDATE DATE NOT NULL WITH DEFAULT)
  Table DELETEME created in ROB. 
> INSERT INTO ROB/DELETEME VALUES('2009-06-20') 
  1 rows inserted in DELETEME in ROB. 
> INSERT INTO ROB/DELETEME VALUES('0001-01-01') 

What was your target file?  I did CSV.

If I don't use the CASE statement I get

CWBDB0099 - No more data is available for the stream fetch request

SQL0181 - Value in date, time, or timestamp string not valid.

Cause . . . . . :   The string representation of a date, time or timestamp 
value is not in the acceptable range.  *N is either the character string 
constant that is not valid or the column or host variable that contained 
the string.  If the name is *N, then the value was found in an expression 
specified in the statement.  If the value was found in a host variable, 
then the host variable number is 0. The proper ranges for date, time, or 
timestamp values are as follows: -- The range for years is from 0001 to 
9999. -- The range for months is from 1 to 12. -- The range for days is 
from 1 - 30 for April, June, September, and November, from 1 - 28 for 
February and from 1 to 31 for all other months.  In a leap year, the range 
for February can be from 1 to 29. -- The range for days in a Julian date 
is from 001 to 366 for a leap year or 001 to 365 days for all other years. 
-- The range for hours is from 0 to 24.  If the hour is 24, then the other 
parts of the time values must be zeros.  If the time format is USA, then 
the hour cannot be greater than 12. -- The range for minutes is from 0 to 
59. -- The range for seconds is from 0 to 59. -- The range for 
microseconds is from 0 to 999999. Recovery  . . . :   Ensure that the 
date, time, or timestamp value conforms to the ranges for the data type it 
represents. Try the request again.

Rob Berendt