Troubleshooting
Problem
This document describes dates in Web Query.
Resolving The Problem
To understand dates in Web Query, we need to know three basic data type concepts: date, date-times, and legacy dates.
For this Knowledge Base document, we will use these definitions.
A date is the date data type. It is a day on a calendar that has an internal representation that can be expressed in any of the date formatting codes that are available.
A date-time is a timestamp data type. This takes the date and appends a time value to it as well.
The last is legacy dates data types. These fields are actually numeric or alphanumeric fields that contain numbers or character strings that represent a date.
o For example, a Decimal 6,0 field might contain 080226 to represent Feb 26, 2008.
o Another possibility is a numeric field that contains a Julian date. We can quickly convert from a Julian legacy date to a Gregorian legacy date using the GREGDT function.
We can convert from legacy dates to a date data type by using a defined field.
An example: In the new defined field box, we are going to put the field name of our numeric or alphanumeric in the main body box.
We will give our new field a name in the upper left Field box.
In the format box we need to tell it how to interrupt the data from the legacy date.
To build this format, we need to combine three pieces of information: data type, length, and order of date elements.
The data type can be one of the following options: A for alphanumeric, I for integer, or P for packed/decimal.
The second is the length, which can be one of two options: 6 or 8.
The last part is the combination and order of date elements.
In Web Query, each single letter represents two digits. Hence, Y is a two-digit year while YY is a four-digit year.
o If our length is six, we then have the choice of any combination of Y, M, and D, such as MDY.
o If our length is eight, then we have the choice of any combination of YY, M, and D, such as MDYY.
Therefore, when we pull the three elements together, we get a formats such as I8YYMD, A6MDY, and so on.
When using date functions, we must be sure to match our input and output types as documented in the Functions reference.
If it is a date function, it will not work on legacy dates field.
Likewise, a date-time function will not work on a date data type field.
The Format box is what sets our output.
We must ensure this is logical for the function.
In some cases, a date might be returned. However, in others, such as DATEDIFF, the result will be an integer.
Date Formatting Codes
Dates can be formatted by any single or combination of the following codes, such as Q or YYQ.
D | Day | It prints a value from 1 to 31 for the day. |
M | Month | It prints a value from 1 to 12 for the month. |
Y | Year | It prints a two-digit year. |
YY | Four-digit year | It prints a four-digit year. |
T | Translate month or day | It prints a three-letter abbreviation for months in uppercase, if M is included in the USAGE specification. |
t | Translate month or day | It functions the same as uppercase T (described above), except that the first letter of the month or day is uppercase and the following letters are lowercase.* |
TR | Translate month or day | It functions the same as uppercase T (described above), except that the entire month or day name is printed rather than an abbreviation. |
tr | Translate month or day | It functions the same as lowercase t (described above), except that the entire month or day name is printed rather than an abbreviation.* |
Q | Quarter | It prints the quarter (1 - 4 if Q is specified by itself, or Q1 - Q4 if it is specified together with other date format items such as Y). |
W | Day-of-Week | If it is included in a USAGE specification with other date component options, it prints a three-letter abbreviation of the day of the week in uppercase. If it is the only date component option in the USAGE specification, it prints the number of the day of the week (1-7, Mon=1). |
w | Day-of-Week | It functions the same as uppercase W (described above), except that the first letter is uppercase and the following letters are lowercase.* |
WR | Day-of-Week | It functions the same as uppercase W (described above), except that the entire day name is printed rather than an abbreviation.* |
wr | Day-of-Week | It functions the same as lowercase w (described above), except that the entire day name is printed rather than an abbreviation.* |
JUL | Julian format | It prints the date in Julian format. |
YYJUL | Julian format | It prints a Julian format date in the format YYYYDDD. The 7-digit format displays the four-digit year and the number of days counting from January 1. For example, January 3, 2001 in Julian format is 2001003. |
Note this TechNote (http://www-01.ibm.com/support/docview.wss?uid=nas8N1020705) describes how to tell Web Query which two digit years should format to a 1900 date and which should format to a 2000 date
Historical Number
480090761
Was this topic helpful?
Document Information
More support for:
IBM i
Software version:
Version Independent
Operating system(s):
IBM i
Document number:
635847
Modified date:
11 November 2019
UID
nas8N1013813