Troubleshooting
Problem
This document explains how Query for i (Query/400) can be used to convert from a date data type to a format of CYYMMDD - Century, Year, Month, Day.
Resolving The Problem
How does a Query for i user convert from a date data type to a date in the format CYYMMDD?
Query for i does not have the prebuilt ability to convert a date data type to a format of CYYMMDD. However, this result can be achieved by using the Result Fields in Query for i
This assumes that the date is originally in a date data type and that your end result would be an alphanumeric field. This process could also be adjusted to have the result field be numeric. The end product will be in the form CYYMMDD. YY represents the last two digits of the year. C represents the century, 0 represents any year of the type 19YY, and 1 represents any year of the type 20YY. MM represents the month. DD represents the day. For example, the string 0970226 would represent February 26, 1997, and the string 1050709 represents July 09, 2005.
This method also works to convert to other forms by modifying the substrings; for example, MMDDYYC.
The following shows how the original date data type can be converted to CYYMMDD.
Step Result fields
Name Expression Column Heading Len D
1 YYYY year(datenorm) 4 0
1 MM month(datenorm) 2 0
1 DD day(datenorm) 2 0
2 CHRYYYY digits(yyyy)
2 CHRMM digits(mm)
2 CHRDD digits(dd)
3 CHRYY substr(chryyyy,3,2)
3 CHRHIGHYY substr(chryyyy,1,2)
4 CONVERT1 '1996-01-01-00.00.00.0000'
||chrhighyy
4 CONVERT2 microsecond(convert1)
5 CONVERT3 convert2 - 19 1 0
6 C digits(convert3)
7 CYYMMDD c||chryy||chrmm||chrdd
Note: The || (pipe-pipe) in Steps 4 and 7 might get converted to ** (asterik-asterik) by some displays
Step 1: The first step is to separate the month, day, and year into separate result fields. This is done with the year, month, and day functions built into Query/400.
Note: If you first created a date data type using the Date function, you will only see a two-digit year displayed, but the year function will produce a four-digit year.
Step 2: The second step is to convert each of these numeric representations of the year, month, and day into character fields. This is done with the digits function.
Step 3: The third step is to split the year into the low and high half. With the year 1985, the high half would be the 19, and the low half would be the 85. To do this we use the substring function.
Step 4: The fourth step is to convert the high part of the year back into a number. There is not a supported function in Query/400 to do this, but the effect can be obtained by using the microsecond function. We define CONVERT1 as a valid timestamp, only the microsecond portion is important. The timestamp can be any date and time, except for the microsecond, which must be all zeros. The last two digits are left off the microsecond for the CHRHIGHYY field, which we will concatenate on the end. When this is done, we define CONVERT2 to take the microsecond of this timestamp. The result is the century, which is now defined as a number rather than a character. This method is also talked about in document N1010676, Query/400 Conversion of a Character Field to Numeric.
Step 5: The fifth step is to get the century number to use. This is done by subtracting 19 from the number we have converted.
Step 6: The sixth step is to convert the numeric representation of the century number into a character field. This is done with the digits function.
Step 7: The seventh step is to put all the parts together again. This is done with the concatenate function.
Note: The length and the decimal field are defined as one and zero, respectively, and if this is not done, the field is too long.
Below, a date data type is converted through some of the steps including sample dates in the original format to the ending format.
DATENORM YYYY MM DD CHRYY CHRMM CHRDD CONVERT3 C CYYMMDD
1854-05-05 1,854 5 5 54 05 05 1- 1 1540505
1963-01-12 1,963 1 12 63 01 12 0 0 0630112
1989-11-23 1,989 11 23 89 11 23 0 0 0891123
1999-12-25 1,999 12 25 99 12 25 0 0 0991225
2008-11-02 2,008 11 2 08 11 02 1 1 1081102
2055-05-05 2,055 5 5 55 05 05 1 1 1550505
Notice that the first date is from the year 1854. This introduces a problem. When the number 18 in CONVERT2 is subtracted from 19 in step 5, a -1 results. The digits function takes the absolute value of a number, therefore the number represented by CYYMMDD is actually not from 1854, but from 2054. To avoid this problem, a query could be run over the data first to ensure that there are no dates from before 1900. Additionally, the following could be added to the select record test section to ensure that only records more recent than the 1900s are converted.
Select record tests
AND/OR Field Test Value (Field, Numbers, or 'Characters')
CONVERT2 GE 19
Note: A detailed explanation of this document might require a consulting agreement.
Query for i does not have the prebuilt ability to convert a date data type to a format of CYYMMDD. However, this result can be achieved by using the Result Fields in Query for i
This assumes that the date is originally in a date data type and that your end result would be an alphanumeric field. This process could also be adjusted to have the result field be numeric. The end product will be in the form CYYMMDD. YY represents the last two digits of the year. C represents the century, 0 represents any year of the type 19YY, and 1 represents any year of the type 20YY. MM represents the month. DD represents the day. For example, the string 0970226 would represent February 26, 1997, and the string 1050709 represents July 09, 2005.
This method also works to convert to other forms by modifying the substrings; for example, MMDDYYC.
The following shows how the original date data type can be converted to CYYMMDD.
Step Result fields
Name Expression Column Heading Len D
1 YYYY year(datenorm) 4 0
1 MM month(datenorm) 2 0
1 DD day(datenorm) 2 0
2 CHRYYYY digits(yyyy)
2 CHRMM digits(mm)
2 CHRDD digits(dd)
3 CHRYY substr(chryyyy,3,2)
3 CHRHIGHYY substr(chryyyy,1,2)
4 CONVERT1 '1996-01-01-00.00.00.0000'
||chrhighyy
4 CONVERT2 microsecond(convert1)
5 CONVERT3 convert2 - 19 1 0
6 C digits(convert3)
7 CYYMMDD c||chryy||chrmm||chrdd
Note: The || (pipe-pipe) in Steps 4 and 7 might get converted to ** (asterik-asterik) by some displays
Updated releases.
Updated product name.
.Step 1: The first step is to separate the month, day, and year into separate result fields. This is done with the year, month, and day functions built into Query/400.
Note: If you first created a date data type using the Date function, you will only see a two-digit year displayed, but the year function will produce a four-digit year.
Step 2: The second step is to convert each of these numeric representations of the year, month, and day into character fields. This is done with the digits function.
Step 3: The third step is to split the year into the low and high half. With the year 1985, the high half would be the 19, and the low half would be the 85. To do this we use the substring function.
Step 4: The fourth step is to convert the high part of the year back into a number. There is not a supported function in Query/400 to do this, but the effect can be obtained by using the microsecond function. We define CONVERT1 as a valid timestamp, only the microsecond portion is important. The timestamp can be any date and time, except for the microsecond, which must be all zeros. The last two digits are left off the microsecond for the CHRHIGHYY field, which we will concatenate on the end. When this is done, we define CONVERT2 to take the microsecond of this timestamp. The result is the century, which is now defined as a number rather than a character. This method is also talked about in document N1010676, Query/400 Conversion of a Character Field to Numeric.
Step 5: The fifth step is to get the century number to use. This is done by subtracting 19 from the number we have converted.
Step 6: The sixth step is to convert the numeric representation of the century number into a character field. This is done with the digits function.
Step 7: The seventh step is to put all the parts together again. This is done with the concatenate function.
Note: The length and the decimal field are defined as one and zero, respectively, and if this is not done, the field is too long.
Below, a date data type is converted through some of the steps including sample dates in the original format to the ending format.
DATENORM YYYY MM DD CHRYY CHRMM CHRDD CONVERT3 C CYYMMDD
1854-05-05 1,854 5 5 54 05 05 1- 1 1540505
1963-01-12 1,963 1 12 63 01 12 0 0 0630112
1989-11-23 1,989 11 23 89 11 23 0 0 0891123
1999-12-25 1,999 12 25 99 12 25 0 0 0991225
2008-11-02 2,008 11 2 08 11 02 1 1 1081102
2055-05-05 2,055 5 5 55 05 05 1 1 1550505
Notice that the first date is from the year 1854. This introduces a problem. When the number 18 in CONVERT2 is subtracted from 19 in step 5, a -1 results. The digits function takes the absolute value of a number, therefore the number represented by CYYMMDD is actually not from 1854, but from 2054. To avoid this problem, a query could be run over the data first to ensure that there are no dates from before 1900. Additionally, the following could be added to the select record test section to ensure that only records more recent than the 1900s are converted.
Select record tests
AND/OR Field Test Value (Field, Numbers, or 'Characters')
CONVERT2 GE 19
Note: A detailed explanation of this document might require a consulting agreement.
Related Information
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CQbAAM","label":"IBM i Db2-\u003EQuery\/400"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Historical Number
15720606
Was this topic helpful?
Document Information
Modified date:
30 November 2024
UID
nas8N1019629