A date wrapper is a way for the query to tell the PC how data in a particular date field is being stored on the IBM Power System (Syste i, iSeries, AS400). This is not the same thing as how the end user wants to see the date data on the PC: that is an edit mask. Since there are different ways to input date formats, date wrappers may vary from table to table or even field to field. Generally, the information stays consistent within tables. When working in ShowCase, it is important to apply the correct date wrapper to the data fields. Applying the correct or incorrect wrapper will affect data coming back as well as conditions that are set up against these date fields.
To see what a date wrapper is, start query. Choose Query\Columns\New. Choose the Time/Date Functions from the Functions & Variables drop-down menu. As you scroll down through the menu, note the options that begin with Date, such as
Date(expression, MMDDYY) or
These are examples of date wrappers; "expression" would be your date field. What do the specific wrappers mean? If we take the example of
Date(expression, MMDDYY), that means that the AS/400 sees the data in MMDDYY format.
There are a few things to check in a date field. To check these, you should still be working in the Query, Columns area.
Now that we have this information, we can determine what date wrapper to use. To give a hint as to what wrapper to use, look at the data that came back in the query. If it looks like 19981231, then the date is in YYYYMMDD. If it looks like 123198, then the date is in MMDDYY format on the AS/400. Be careful when analyzing this: something like 980112 might be YYMMDD or YYDDMM. Check several records of the data to determine what the AS/400 date format is.
Now that you how your data is coming back from the iSeries, you must apply the date wrapper to the date field using the following:
At this point, when you run the query, the date should come back correctly. It will not come back in the format of the date wrapper applied, but in the format that your PC has set up in the Regional Settings. To change the way this date looks in the results, the edit mask function will be used.
For more information, please consult the online help in ShowCase Query, the keywords being DATE FORMAT.
Still have questions? We can help. Submit a case to Technical Support.