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 error, etc.

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 DFT('0001-01-01')

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?

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.