Re: Invalid dates in data transfer

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