Note, you didn't specify where you are doing this, but since you tagged as ibm-midrange, I am answering for embedded SQL. If you want JDBC, or ODBC, or interactive SQL, the concept is similar, just the means of achieving it is different.
Make sure SQL is using dates in the correct format, it defaults to *ISO
. For you it should be *EUR
. In RPG, you can do it this way:
exec sql set option *datfmt = *EUR;
Make sure that set option
is the first SQL statement in your program, I generally put it immediately between D and C specs.
Note that this is not an optimal solution for a program. Best practice is to set the RPG and SQL date formats both to *ISO. I like to do that explicitly. RPG date format is set by
ctl-opt DatFmt(*ISO);
SQL date format is set by
exec sql set option *datfmt = *ISO;
Now all internal dates are processed in *ISO format, and have no year range limitation (year can be 0001 - 9999). And you can display or print in any format you please. Likewise, you can receive input in any format you please.
Edit Dates are a unique beast. Not every language, nor OS knows how to handle them. If you are looking for a Date value, the only format you need to specify is the format of the string you are converting to a Date. You don't need to (can't) specify the internal format of the Date field, and the external format of a Date field can be mostly anything you want, and different each time you use it. So when you use TIMESTAMP_FORMAT()
as @Stavr00 mentioned:
DATE(TIMESTAMP_FORMAT(CHAR(OAORDT),'YYYYMMDD'))
The format provided is not the format of the Date field, but the format of the data being converted to a Timestamp
. Then the Date()
function converts the Timestamp
value into a Date
value. At this point format doesn't matter because regardless of which external format you have specified by *DATFMT
, the timestamp is in the internal timestamp format, and the date value is in the internal date format. The next time the format matters is when you present the Date value to a user as a string or number. At that point the format can be set to *ISO
, *EUR
, *USA
, *JIS
, *YMD
, *MDY
, *DMY
, or *JUL
, and in some cases *LONGJUL
and the *Cxxx
formats are available.
*EUR
, do it in*ISO
:'YYYY-MM-DD'
. – Clockwork-MuseDate
value. This is a thing with an internal format that you never see, and, as far as I can tell, IBM has never published it. A Date field also has an external format and that format is controlled by the*DATFMT
option. All that to say that if you want aDate
value, the only format that matters is the format of the string being converted to a date. See my answer. – jmarkmurphy