Troubleshooting
Problem
Learn how the original form of date data in Long Julian, YYYYDDD, can be converted to a *USA format of MM/DD/YY or MM/DD/YYYY.
Resolving The Problem
Note: Detailed explanations of this document may best be handled through a consulting agreement.
Query for i (Query/400) does not have the pre-built ability to convert a 7-digit numeric field representing an Unformatted Julian date to a date with a 4-digit year. However, this result can be achieved by using the Result Fields in Query/400.
The following shows how the original form YYYYDDD can be converted to MM/DD/YY or MM/DD/YYYY, which is the *USA format.
This assumes that the date is originally in a numeric (see Section 1) or character (see Section 2) field rather than a date data type field. In this example, we are starting with the representation YYYYDDD. YYYY represents the four digits of the year. DDDD represents the day of the year.
Section 1: Converting Where the Unformatted Julian Date Is a Numeric Data Type
The result fields must be set up like the following:
Result fields
Step Name Expression Column Heading Len Dec
1 MISC1 y4julian / 1000 4 0
1 JULYEAR misc1 * 1000 7 0
1 JULDAYS y4julian - julyear
2 CHARJUL digits(y4julian)
2 YYYY substr(charjul,1,4)
3 BUILDREG1 '01/01/'||yyyy
3 BUILDREG2 date(buildreg1)
3 REGDATE buildreg2 + juldays days - 1 days
4 USAREGDATE char (regdate, usa)
Step 1
To get the Julian Days by itself in a numeric format, divide the date by 1000 and ignoring the decimal and then multiplying it back by 1000. Please note that MISC1 and JULYEAR have a length and decimal specified. Without those specified, this will not work. Then JULYEAR is subtracted from the original date Y4JULIAN to get the Julian days by themselves in numeric format.
Step 2
To get the Julian Year by itself in a character format, convert the original date to a character format and substringing out the relevant data.
Step 3
The date data type must be built. This is done by building a valid date with the month as January for now and the day as 01 for now. This is then changed to a date data type. Then, the proper month and days are added again. Please note that the BUILDREG1 should have a pipe-pipe before the YYYY, some fax machines will change this to a star-star. The pipe is the vertical line usually a part of the \ key.
Step 4
This shows how to display the date in a mm/dd/yyyy format, using the USA standard. For more information on other standards for displaying dates that Query can use, see the Query/400 book under the chapter: Defining Result Fields and the section: Date, Time, and Timestamp Expressions. When changing to this format, the data becomes a character data type.
Using these methods, other date representation is also possible.
Below, YYYYDDD is converted through some of the steps.
Y4JULIAN MISC1 JULYEAR JULDAYS YYYY BUILDREG2 REGDATE USAREGDATE
1,993,059 1,993 1,993,000 59 1993 01/01/93 02/28/93 02/28/1993
1,993,105 1,993 1,993,000 105 1993 01/01/93 04/15/93 04/15/1993
1,993,191 1,993 1,993,000 191 1993 01/01/93 07/10/93 07/10/1993
1,938,105 1,938 1,938,000 105 1938 ++++++++ ++++++++ 04/15/1938
The year 1938 does not display in the MM/DD/YY format because it is not within the range of dates that is supported for the 2-year format. To use the 2-year format, the dates must fall between 1940 and 2039. For more information on this, refer to the Query/400 Use book under the chapter: Defining Result Fiends in Query/400. This date does display correctly when converted to the *USA format.
Section 2: Converting Where the Unformatted Julian Date Is a Character Data Type
The result fields must be set up as follows:
Result fields
Step Name Expression Column Heading Len D
1 MISC1 substr(y4charjul,5,3)
1 MISC2 '1996-01-01-00.00.00.000'||misc1
1 JULDAYS microsecond(misc2)
2 YYYY substr(y4charjul,1,4)
3 BUILDREG1 '01/01/'||yyyy
3 BUILDREG2 date(buildreg1)
3 REGDATE buildreg2 + juldays days - 1 days
4 USAREGDATE char(regdate, usa)
Step 1
The first step is to get the Julian Days by itself in a numeric format. This is done by substringing the day data from the field. This data is now in character format but needs to be in numeric format. There is not a supported function in Query/400 to do this; however, the effect can be obtained by using the micro-second function. We define MISC2 as a valid timestamp, only the micro-second portion is important. The timestamp can be any date and time, except for the micro-second which needs to be all zeros, except for the last 3 digits which is concatenated on the end. When this is done, we define JULDAYS to take the micro-second of the timestamp. The result is that the Julian Days is now in a numeric format. This method is also talked about in Document N1010676, Query/400 Conversion of a Character Field to Numeric.
Step 2
To get the Julian Year by itself in a character format, substring the year data from the field.
Step 3
To build the data type, build a valid date with the month as January for now and the day as 01 for now. This is then changed to a date data type. Then, the proper month and days are added. The BUILDREG1 should have a pipe-pipe before the YYYY, some fax machines will change this to a star-star. The pipe is the vertical line usually a part of the \ key.
Step 4
This shows how to display the date in a mm/dd/yyyy format using the USA standard. For more information on other standards for displaying dates that Query can use, see the Query for i manual under the chapter: Defining Result Fields and the section: Date, Time, and Timestamp Expressions. When changing to this format, the data becomes a character data type.
Using these methods, other date representation is also possible.
Below YYYYDDD is converted through some of the steps.
MISC1 JULDAYS YYYY BUILDREG2 REGDATE USAREGDATE
059 59 1993 01/01/93 02/28/93 02/28/1993
105 105 1993 01/01/93 04/15/93 04/15/1993
191 191 1993 01/01/93 07/10/93 07/10/1993
105 105 1938 ++++++++ ++++++++ 04/15/1938
The year 1938 will not display in the MM/DD/YY format because it is not within the range of dates that is supported for the 2-year format. Using the 2-year format the dates must fall between 1940 and 2039. For more information on this, refer to the Query for i Use book under the Chapter: Defining Result Fields in Query for i. This date does display correctly when converted to the *USA format.
Query for i (Query/400) does not have the pre-built ability to convert a 7-digit numeric field representing an Unformatted Julian date to a date with a 4-digit year. However, this result can be achieved by using the Result Fields in Query/400.
The following shows how the original form YYYYDDD can be converted to MM/DD/YY or MM/DD/YYYY, which is the *USA format.
This assumes that the date is originally in a numeric (see Section 1) or character (see Section 2) field rather than a date data type field. In this example, we are starting with the representation YYYYDDD. YYYY represents the four digits of the year. DDDD represents the day of the year.
Section 1: Converting Where the Unformatted Julian Date Is a Numeric Data Type
The result fields must be set up like the following:
Result fields
Step Name Expression Column Heading Len Dec
1 MISC1 y4julian / 1000 4 0
1 JULYEAR misc1 * 1000 7 0
1 JULDAYS y4julian - julyear
2 CHARJUL digits(y4julian)
2 YYYY substr(charjul,1,4)
3 BUILDREG1 '01/01/'||yyyy
3 BUILDREG2 date(buildreg1)
3 REGDATE buildreg2 + juldays days - 1 days
4 USAREGDATE char (regdate, usa)
Step 1
To get the Julian Days by itself in a numeric format, divide the date by 1000 and ignoring the decimal and then multiplying it back by 1000. Please note that MISC1 and JULYEAR have a length and decimal specified. Without those specified, this will not work. Then JULYEAR is subtracted from the original date Y4JULIAN to get the Julian days by themselves in numeric format.
Step 2
To get the Julian Year by itself in a character format, convert the original date to a character format and substringing out the relevant data.
Step 3
The date data type must be built. This is done by building a valid date with the month as January for now and the day as 01 for now. This is then changed to a date data type. Then, the proper month and days are added again. Please note that the BUILDREG1 should have a pipe-pipe before the YYYY, some fax machines will change this to a star-star. The pipe is the vertical line usually a part of the \ key.
Step 4
This shows how to display the date in a mm/dd/yyyy format, using the USA standard. For more information on other standards for displaying dates that Query can use, see the Query/400 book under the chapter: Defining Result Fields and the section: Date, Time, and Timestamp Expressions. When changing to this format, the data becomes a character data type.
Using these methods, other date representation is also possible.
Below, YYYYDDD is converted through some of the steps.
Y4JULIAN MISC1 JULYEAR JULDAYS YYYY BUILDREG2 REGDATE USAREGDATE
1,993,059 1,993 1,993,000 59 1993 01/01/93 02/28/93 02/28/1993
1,993,105 1,993 1,993,000 105 1993 01/01/93 04/15/93 04/15/1993
1,993,191 1,993 1,993,000 191 1993 01/01/93 07/10/93 07/10/1993
1,938,105 1,938 1,938,000 105 1938 ++++++++ ++++++++ 04/15/1938
The year 1938 does not display in the MM/DD/YY format because it is not within the range of dates that is supported for the 2-year format. To use the 2-year format, the dates must fall between 1940 and 2039. For more information on this, refer to the Query/400 Use book under the chapter: Defining Result Fiends in Query/400. This date does display correctly when converted to the *USA format.
Section 2: Converting Where the Unformatted Julian Date Is a Character Data Type
The result fields must be set up as follows:
Result fields
Step Name Expression Column Heading Len D
1 MISC1 substr(y4charjul,5,3)
1 MISC2 '1996-01-01-00.00.00.000'||misc1
1 JULDAYS microsecond(misc2)
2 YYYY substr(y4charjul,1,4)
3 BUILDREG1 '01/01/'||yyyy
3 BUILDREG2 date(buildreg1)
3 REGDATE buildreg2 + juldays days - 1 days
4 USAREGDATE char(regdate, usa)
Step 1
The first step is to get the Julian Days by itself in a numeric format. This is done by substringing the day data from the field. This data is now in character format but needs to be in numeric format. There is not a supported function in Query/400 to do this; however, the effect can be obtained by using the micro-second function. We define MISC2 as a valid timestamp, only the micro-second portion is important. The timestamp can be any date and time, except for the micro-second which needs to be all zeros, except for the last 3 digits which is concatenated on the end. When this is done, we define JULDAYS to take the micro-second of the timestamp. The result is that the Julian Days is now in a numeric format. This method is also talked about in Document N1010676, Query/400 Conversion of a Character Field to Numeric.
Step 2
To get the Julian Year by itself in a character format, substring the year data from the field.
Step 3
To build the data type, build a valid date with the month as January for now and the day as 01 for now. This is then changed to a date data type. Then, the proper month and days are added. The BUILDREG1 should have a pipe-pipe before the YYYY, some fax machines will change this to a star-star. The pipe is the vertical line usually a part of the \ key.
Step 4
This shows how to display the date in a mm/dd/yyyy format using the USA standard. For more information on other standards for displaying dates that Query can use, see the Query for i manual under the chapter: Defining Result Fields and the section: Date, Time, and Timestamp Expressions. When changing to this format, the data becomes a character data type.
Using these methods, other date representation is also possible.
Below YYYYDDD is converted through some of the steps.
MISC1 JULDAYS YYYY BUILDREG2 REGDATE USAREGDATE
059 59 1993 01/01/93 02/28/93 02/28/1993
105 105 1993 01/01/93 04/15/93 04/15/1993
191 191 1993 01/01/93 07/10/93 07/10/1993
105 105 1938 ++++++++ ++++++++ 04/15/1938
The year 1938 will not display in the MM/DD/YY format because it is not within the range of dates that is supported for the 2-year format. Using the 2-year format the dates must fall between 1940 and 2039. For more information on this, refer to the Query for i Use book under the Chapter: Defining Result Fields in Query for i. This date does display correctly when converted to the *USA format.
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
16315857
Was this topic helpful?
Document Information
More support for:
IBM i
Component:
IBM i Db2->Query/400
Software version:
All Versions
Operating system(s):
IBM i
Document number:
644695
Modified date:
30 November 2024
UID
nas8N1019622
Manage My Notification Subscriptions