DateDiff
This field level rule lets you display the difference between two dates. The dates do not have to be in the same format in the extract file, but the format of each must be supported. (The valid formats 1,7,10,11 will be described later.)
The dates and their formats are separated by a comma (,) and are defined in the Data field of the rule. For formats supporting a two-digit year, the 20th century (19xx) is assumed for date difference calculations. Date differences as a result of using this rule are not true in the sense that they are calculated under the following guidelines. These are sometimes referred to as bankers dates.
- Month = 30 days
- Year = 360 days
You must also specify the output format and output format data in the format mask of the rule. (The only valid format and data currently supported is 1 3 / 3.)
Syntax
FORMATMASK;rule;RULEDATA;...
The FormatMask must be in the form of:
OUTPUTFORMAT OUTPUTFORMATDATA
where OUTPUTFORMATDATA varies, depending on the output format. You must separate these parameters with a space.
OutputFormat
Shows the difference between the two dates, in months and days. The only output format is 1. You must separate these parameters with a space.
OutputFormatData
The OutputFormatData must be in the form of:
MONTHLENGTH SEPARATORSTRING DAYLENGTH
For example, if you enter 3 / 3 and there are 365 days difference, the output would be 012/005. Here is an example:
1 3 / 3 (1 is OUTPUTFORMAT)
The RULEDATA consists of two groups of search criteria and extract field descriptors for the two dates. These two groups of data are separated by a colon (:). Within each group, search criteria and extract field descriptors are separated by a single space. Within search criteria, offset and length are separated by a comma (,). Extract field descriptors are also separated by a comma (,).
The RULEDATA must be in this form:
RECOFFSET1,RECDATA1 OFFSET1,LENGTH1,DATEFORMAT1:RECOFFSET2,RECDATA2 OFFSET2,LENGTH2,DATEFORMAT2
Parameter |
Description |
RECOFFSET1 |
The record offset of first date. |
RECDATA1 |
The search data for record of first date. |
0FFSET1 |
The offset of first date in record. |
LENGTH1 |
The length of first date in record. |
*DATEFORMAT1 |
The format of first date. |
RECOFFSET2 |
The record offset of second date |
RECDATA2 |
The search data for record of second date |
OFFSET2 |
The offset of second date in record |
LENGTH2 |
The length of second date in record |
*DATEFORMAT2 |
The format of second date |
* The format you specify here must be supported. |
You can use these formats:
Enter |
For this format: |
1 |
MM/DD/YY |
7 |
YYYY-MM-DD |
8 |
|
10 |
Month Date, Year (such as February 17, 2002) |
11 |
MMDDYYYY |
Note | The data for the two dates is separated by a colon (:). Here is a RULEDATA example: 1,TRANS 73,10,7:1,TRANS 83,10,7 |
© Copyright 2020, Oracle and/or its affiliates. All rights reserved. Legal notices.