Understanding Date Functions and Date Wrappers

 

Date values can be stored as different data types: Date, Decimal, Numeric, and Character. With the data stored this way it becomes difficult to use the raw data in any type of conditioning or calculation. The way to work around this problem is to use date wrappers so the values will be interpreted as a date and not a numeric or character value. The following paragraphs will attempt to give a clearer understanding of the use of the date wrappers as well as a few examples to build upon.

Date wrappers are provided to modify the appearance of the stored date information so it appears in a more recognizable format and also so that the data is properly recognized as a valid date. The different date wrappers can be accessed within Query or Report Writer by going to Query || Columns || New and then choosing General Expressions. This will take you to the General Expressions dialog box where you will see a section titled Functions and Variables. Use the drop down here to select Time/Date Functions. Within this category you will find a number of functions available and as you scroll down through the options you will see the expressions such as, DATE(expression, YYMMDD), for example. The YYMMDD would be the format that the date is stored in on the AS/400. Highlighting one of the options and then pressing F1 will bring up the help text on that particular function and will include the syntax. Many options are available to choose from for a date wrapper so knowing how your data is stored on the AS/400 and in what format, is the key in choosing the proper wrapper. Below are a few examples of how the date may be stored on the AS/400 and the date wrapper that would be used for that format. The three date wrappers used are:

1. DATE(expression,CYYDDD) Julian Wrapper
2. DATE(expression,HYF) Hundred Year Format date wrapper
3. DATE(expression,YYYYMMDD) four digit year date wrapper

Within the Time/Date Functions there are also other options available, which reference other important functions. A few of these would be the CURDATE() or Current_Date function, the DAY(expression) wrapper, the WEEK(date_expression) wrapper, the MONTH(date_expression) wrapper, and the YEAR(date_expression) wrapper. Of course there are others available as well, but too numerous to list. Once again if you are not sure how to use the function, just highlight the function you would like to use and press F1. An explanation of what the function does and the syntax as well as an example or two will be displayed to help in the understanding and use of it. The combination of the date wrappers and the other functions provide a powerful tool by which conditions to eliminate and return only the desired data can be created. We will devote the rest of this article to that end and take a look at a few examples of conditions that can be created with these functions.

Scenario 1: How can I create a condition to return the data from the first six months of the year? What functions could be used to accomplish this task?
There are a couple of different ways to accomplish this and we will take a look at them. First of all, we could just set up a condition that would require the dates to fall between 1/1/1994 and 6/30/1994. We would use the condition setup as:

DATE(Datinv,YYYYMMDD) BETWEEN 1/1/1994 AND 6/30/1994.

The other option would involve using the MONTH and YEAR wrappers, which returns the numeric value for that particular month and would look like:

MONTH(DATE(DATINV,YYYYMMDD)) BETWEEN 1 AND 6
AND YEAR(DATE(DATINV,YYYYMMDD)) = 1994

Scenario 2: How can I create a condition to bring back all the records that are only six days old? What function would be used to accomplish this?
The best way to set this condition would be to use the CURDATE() function and your date field. Depending upon how your date is stored you would then use the corresponding date wrapper so that the date will be recognized properly. For this example we will again assume that the data is stored in the YYYYMMDD format and the condition would look like:

DATE(DATINV,YYYYMMDD) = CURDATE() - 7 days

Scenario 3: How can I determine the number of days that have elapsed between the current date and the date the record was created? What function or functions would need to be used?
To accomplish this you would need to use a combination of the DAYS wrapper, the CURDATE() function, and your date field with the correct date wrapper to create a new column that displays the number of elapsed days. Again we will assume that the date stored on the AS/400 is in the YYYYMMDD format. We would need to go to Query || Columns and select NEW || General Expression and this would take us to the new columns dialog box. The column we would create would contain the following:

DAYS(CURDATE()) - DAYS(DATE(DATESHIP,YYYYMMDD))

Ref#: 1479789