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 Date(expression, CYYDDD).
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.
Check which date wrapper is needed for your date fields
There are a few things to check in a date field. To check these, you should still be working in the Query, Columns area.
- In the Columns area, find the date field in question. What is the date format, character, numeric, decimal or date? What is the number next to the format type (e.g., 6, 8, 10)
- Add the field to the Added Columns list. Keep that date field highlighted and click Edit. Does the date field look something like INVDATE or more like Date(INVDATE, YYMMDD)? If it looks like the first example, then a date wrapper is needed. If it looks more like the second, then a date wrapper has already been applied.
- If there is no date wrapper applied, run a query using the date field in question, without adding any wrappers. How is the information coming back? As 19981231 or 123198 or something completely different?
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:
- Within the query, choose Query\Columns.
- Highlight the date field in the Added Columns list and choose Edit.
- Keeping the date field highlighted, choose Time/Date Functions from the Functions & Variables drop-down menu.
- Choose the appropriate date wrapper from the list.Highlight it and click Add, or just double-click the date wrapper. The date wrapper should replace the date field in the Edit Column window.
- Highlight the word "expression", if it is not already. In the upper right corner of the Edit Columns dialog box, choose the All Selected Columns option from the Columns drop-down menu.
- Find the appropriate date field. Highlight the field and choose Add, or double-click on the field name. The result should look similar to the following:
- Click OK to save this column and go back to the Query, Columns area.
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.
Ref#: 1478117
