DIGITS is a function provided by Query/400. The value in CORDDATE for record 2 is '940615'. CORDDATE is a character field with length 6. Note: CORDDATE is a field name I made up to hold values from ORDDATE. Using Define Result Field screen, enter the following: Step 3A: Convert Numeric to Character Data Step C creates a date format field from step B.īefore illustrating steps A through C, I will describe the sample database used in the illustration. Step B arranges the data to match the format of the system date using the SUBSTR (stands for substring) function and concatenation operators (││). Step A converts numeric data to character data using the DIGITS function.

The conversion is accomplished in three steps. Step 3: Convert Numeric Data to DATE Format CURRENT and DATE are functions provided by Query/400. SYSDAT has a length of 8 and format type of L (meaning date). Note: SYSDAT is a field name I made up to represent today's date. You can make today's date available to the query by creating the following result field: This means that the DATE format is MM/DD/YY. One way is to issue CHGJOB, then prompt, then press F10 for more parameters and page down one time. See the steps below for the details for each entry. This is the Define Result Fields portion of the query. N1010680, Query/400 Conversion of a MDY Field to a Date Data Type: N1010673, Query/400 Converting a YMD Field to a Date Data Type :įor an example for MMDDYY conversion to date data type: The meaning of the digits reading left to right in field ORDDATE are month (2 digits), day (2 digits) and year (2 digits).įor more examples, refer to the following Rochester Support Center knowledgebase documents:įor an example for YYMMDD conversion to date data type: This example is where the 'date' field, ORDDATE, is defined as numeric 6 positions with 0 decimal field in MMDDYY format. Note: Read Chapter 5 of the Query/400 Use book for background on this process. Other illustrations can be found in the on-line manual, Query for iSeries Use, which is located at the following Knowledge Center Web site:įollowing are the general steps used to do a comparison to the System Date when the 'date' field is numeric:ĭetermine DATE format for the job that will run the query.Ĭonvert numeric data to DATE format to match the DATE format of the job. Therefore, the numbers in the numeric field need to be converted to DATE format before they can be used for comparison with today's date. Query only works with data in numeric fields as numbers. However, many databases were designed to put date information in numeric fields. On the surface, this is a simple process if the fields being compared are already in DATE format. This can be accomplished using the CURRENT(DATE) function provided by Query/400. Query/400 can be used to extract data from a file based on today's date, without having to change the query every day.

Dbvisualizer current date