IBM Support

Web Query - DATE Information

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.

DDayIt prints a value from 1 to 31 for the day.
MMonthIt prints a value from 1 to 12 for the month.
YYearIt prints a two-digit year.
YYFour-digit yearIt prints a four-digit year.
TTranslate month or dayIt prints a three-letter abbreviation for months in uppercase, if M is included in the USAGE specification.
tTranslate month or dayIt 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.*
TRTranslate month or dayIt functions the same as uppercase T (described above), except that the entire month or day name is printed rather than an abbreviation.
trTranslate month or dayIt functions the same as lowercase t (described above), except that the entire month or day name is printed rather than an abbreviation.*
QQuarterIt 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).
WDay-of-WeekIf 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).
wDay-of-WeekIt functions the same as uppercase W (described above), except that the first letter is uppercase and the following letters are lowercase.*
WRDay-of-WeekIt functions the same as uppercase W (described above), except that the entire day name is printed rather than an abbreviation.*
wrDay-of-WeekIt functions the same as lowercase w (described above), except that the entire day name is printed rather than an abbreviation.*
JULJulian formatIt prints the date in Julian format.
YYJUL
 
Julian formatIt 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
[{"Product":{"code":"SWG60","label":"IBM i"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"DB2 Web Query","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

480090761

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

Manage My Notification Subscriptions