I'd like to illustrate the method I use to have the substring functions in Query/400 and SQL work on a numeric field. My example operates on an order file, and what I'm going to demonstrate is how to extract the year out of a numeric date. To accomplish this, I use a combination of the DIGITS function and the SUBSTR function in Query/400.

In my example order file, I have a field called FDATE. Because FDATE is numeric, Query (and SQL) cannot use SUBSTR to extract the year. Fortunately, with the help of the DIGITS keyword, my query is able to convert FDATE to an alphanumeric value, which then allows the query to perform a substring. In 2, you can see how to combine the two keywords. The result of the expression is placed in a field I defined in Query/400 and called YEAR.

In my example order file, I have a field called FDATE. Because FDATE is numeric, Query (and SQL) cannot use SUBSTR to extract the year. Fortunately, with the help of the DIGITS keyword, my query is able to convert FDATE to an alphanumeric value, which then allows the query to perform a substring. In Figure 2, you can see how to combine the two keywords. The result of the expression is placed in a field I defined in Query/400 and called YEAR.

Now I can simply use the YEAR variable I just created to select records from the file for the year 95. You can see an example of the selection process in 3. The method I've come up with is simple, and it also works in SQL.

Now I can simply use the YEAR variable I just created to select records from the file for the year 95. You can see an example of the selection process in Figure 3. The method I've come up with is simple, and it also works in SQL.

- Jean-Jacques Risch

TechTalk: Using character functions in Query and SQL on numeric fields.

Figure 2: Using Functions to Extract the Year from a Numeric Date

TechTalk: Using character functions in Query and SQL on numeric fields.

Figure 3: Using Resulting Character Year Field