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

Number

Julian Date

Julian Wrapped Date CYYDDD

YYYYMMDD Format

YYYYMMDD Format Wrapped Date

Hundred Year Format (HYF)

HYF Wrapped Date

30000

99284

10/11/1999

19991011

10/11/1999

36473

11/10/1999

30001

99006

1/6/1999

19990106

1/6/1999

36195

2/5/1999

30002

99037

2/6/1999

19990206

2/6/1999

36226

3/8/1999

30003

99061

3/2/1999

19990302

3/2/1999

36250

4/1/1999

30004

99116

4/26/1999

19990426

4/26/1999

36305

5/26/1999

30005

99125

5/5/1999

19990505

5/5/1999

36314

6/4/1999

30006

99175

6/24/1999

19990624

6/24/1999

36364

7/24/1999

30007

99187

7/6/1999

19990706

7/6/1999

36376

8/5/1999

30008

99216

8/4/1999

19990804

8/4/1999

36405

9/3/1999

30009

99258

9/15/1999

19990915

9/15/1999

36447

10/15/1999

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.

Employee Id

Customer Id

Invoice Id

Date of Invoice

Total Billed

Total Paid

CAB

51

10050

6/10/1994

716.80

.00

CAB

61

10058

6/21/1994

603.55

503.55

KAT

24

10011

6/10/1994

1,536.58

1,736.58

KAT

55

10052

6/20/1994

658.11

358.11

KAT

63

10060

6/25/1994

2,061.67

.00

SAP

56

10055

6/20/1994

506.84

.00

SAP

65

10061

6/25/1994

4,213.02

.00

SAS

5

10043

1/23/1994

.00

.00

SAS

5

10010

6/6/1994

2,958.15

2,958.15

SAS

14

10012

6/11/1994

1,015.77

.00

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

Employee Id

Customer Id

Invoice Id

Date of Invoice

Month

Total Billed

Total Paid

CAB

51

10050

6/10/1994

6

716.80

.00

CAB

61

10058

6/21/1994

6

603.55

503.55

KAT

24

10011

6/10/1994

6

1,536.58

1,736.58

KAT

55

10052

6/20/1994

6

658.11

358.11

KAT

63

10060

6/25/1994

6

2,061.67

.00

SAP

56

10055

6/20/1994

6

506.84

.00

SAP

65

10061

6/25/1994

6

4,213.02

.00

SAS

5

10010

6/6/1994

6

2,958.15

2,958.15

SAS

5

10043

1/23/1994

1

.00

.00

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

Customer ID

Order Number

Date Ordered

Date Shipped

CURDATE( )

Elapsed Days

114801

41777

1/4/2003

1/8/2003

1/15/2003

7

330004

41931

1/8/2003

1/8/2003

1/15/2003

7

330004

41931

1/8/2003

1/8/2003

1/15/2003

7

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))

Customer ID

Order Number

Date Ordered

Date Shipped

CURDATE()

Elapsed Days

110701

40607

12/26/2002

1/11/2003

1/15/2003

4

113801

33728

1/8/2003

1/12/2003

1/15/2003

3

114801

41777

1/4/2003

1/8/2003

 

 

 

Ref#: 1479789

 

 

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017