CODEWITHSUNDEEP
sqldateibm-midrangeiseries-navigator
Harold Decapia
Harold Decapia

Reputation: 385

AS400 Emulator cant process dates less than 1940 or greater than 2039

I have this simple SQL script:

SELECT DATE(SUBSTR( '19310205' , 1 , 4) 
|| '-' || SUBSTR ('19310205' , 5 , 2) 
|| '-' || SUBSTR('19310205', 7 , 2)) 
FROM MY_TABLE;

Whenever I run the script on the iSeries navigator, I get the expected output which is

1931-02-05

However, when I run it on the AS400 Emulator using the strsql command, I get the following output:

++++++++

I have no idea why this happens; all I know is that only the dates between 1940-2039 are working well. Can anyone explain why?

Also, is there a way for the other dates be processed successfully as well?

Upvotes: 0

Views: 2453

Answers (3)

danny117
danny117

Reputation: 5651

Return a character representation of a date in any format using the varchar_format() function. Regardless of session defaults or where its run from.

SELECT                               
cast(                                
varchar_format(                      
cast(                                
DATE(SUBSTR( '19310205' , 1 , 4)     
|| '-' || SUBSTR ('19310205', 5 , 2) 
|| '-' || SUBSTR('19310205', 7 ,2))  
 as timestamp )                      
, 'YYYY-MM-DD')                      
as char(10)) as mydate               
 FROM sysibm/sysdummy1

MYDATE
1931-02-05
******** End of data ********

Upvotes: 0

jmarkmurphy
jmarkmurphy

Reputation: 11493

I just want to build on Richard Evans answer, but since there is too much to put into a comment, I will make it a new answer.

IBM i has a native date type that can store dates from January 1, 0001 to December 31, 9999. The date type has a format which specifies, among other things, the number of digits in the year portion of the date. There are two main groupings that affect the range of dates that can be accepted. Two digit year formats which can accept dates from January 1, 1940 - December 31, 2039. These are: *MDY, *DMY, *YMD, and *JUL. Four digit year formats can handle the full range of dates that the date type supports. These are *ISO, *USA, *EUR, *JIS.

In addition to the main formats which are supported everywhere date formats are supported, RPG supports a few other date formats: *JOBRUN, three digit year formats, and *LONGJUL. *JOBRUN is a two digit year format, and can only be specified for numeric and character date fields with 2 digit years. This uses the job date format and seperator values. Three digit year formats can handle dates from January 1, 1900 - December 31, 2899. These are *CMDY, *CDMY, and *CYMD. The C in the three digit year formats represents the two most significant digits of the year portion of the date where: 

0 => 1900 - 1999
1 => 2000 - 2099
2 => 2100 - 2199
...
9 => 2800 - 2899

Finally *LONGJUL is a four digit year Julian format.

Internally dates are stored in an *ISO format. The other formats are external formats. In fact when defining tables with SQL you don't even get to specify the date format, it just defaults to *ISO. In places that you can specify the format, it is just an external format that you are specifying. It is best to use *ISO for all working fields and database files, even when using DDS. Then reserve other external formats like *MDY or *DMY for user facing fields to convert the *ISO format into a more localized format. This way you can store the full range of dates that users can input or view.

Upvotes: 2

Richard Evans
Richard Evans

Reputation: 384

  • In your AS/400 emulator session, press "F13=Services".

  • Select "1. Change Session Attributes"

  • Change the "Date Format" to "*ISO" or desired format supporting a full date range.

From the IBM Knowledge Center, "Rational Developer for i7.1.0":

A date is a three-part value (year, month, and day) designating a point in time >under the Gregorian calendar20, which is assumed to have been in effect from >the year 1 A.D. The range of the year part is 0001 to 9999. The date formats >*JUL, *MDY, *DMY, and *YMD can only represent dates in the range 1940 through >2039. The range of the month part is 1 to 12. The range of the day part is 1 to >x, where x is 28, 29, 30, or 31, depending on the month and year.

Upvotes: 4

Related Questions