Chuck said this already, but in case he didn't hammer the point home...
Although your date is defined as *ISO in the file, when SQL displays it,
it will convert it to whatever the SQL date format is set to.
For example, in STRSQL, hit F13 and option 1. On my system, the date
format is *MDY. This will fail with your dates that are 0001-01-01
purely because that can't be converted to *MDY format. But, if I change
my format to *ISO or *USA it works fine.
Same is true of embedded SQL in RPG... the CRTSQLRPGI command has a
DATFMT parameter that specifies what SQL will convert to. You can also
change this with "set option". But, if it's a format with a 2-digit
year, your 0001-01-01 won't be able to convert to that, and will give an
Hope that's clear -- the issue isn't that you used DDS or that you have
a DFT keyword. Rather, the issue is that the year isn't between
1940-2039 and therefore cannot be converted to a 2-digit year format,
which SQL will attempt to do if your date format is set to something
like *MDY or *DMY.
IMHO, the solution is simple... set your SQL date format to one that
supports a 4-digit year. That's a much better solution, imho, than
saying something like "don't use DFT" -- nothing wrong with DFT.
On 9/29/2015 11:26 AM, Justin Taylor wrote:
I have a DDS PF that contains a date field defined:
A DATEFLD L DATFMT(*ISO)
RPGLE, DSPPFM and QRY/400 are all fine with that and show the field
correctly. SQL chokes on it however. It returns a null value and a
01534 SQLSTATE (The string representation of a datetime value is
Any ideas? Is it the DFT keyword?