D Code - Date Conversion
The D code converts dates from internal to output format and from output to internal format.
Input conversion
Input conversion is valid, but Special Processing Operators except S are ignored. See Input Conversion: Notes and Examples.
Input/Output or pre-processor
D codes are generally specified as input/output (attribute 7) codes since dates need to be in internal format (an integer representing days before or after the 31st December 1967) for most selection and sort processing.
However, special processing operators, apart from S, must be specified as pre-processor (attribute 8) codes to enable selection or sort processing.
Syntax
D{operator}{yearFormat}{inputField}{outputSeparator}
Syntax elements
operator Special Processing Operators.
yearFormat A number (0-4) that specifies the number of digits (starting from the right) to be output for the year field. If omitted, the processor outputs a four-digit year. If yearFormat is 0, all digits for the year are suppressed.
inputField An input field specification consisting of a field delimiter character followed by the number of fields that must be skipped to locate the date field. See Dates Within Strings for more details.
outputSeparator A non-numeric character to separate the output fields (day, month and year). Cannot be a special processing operator. The default is a space.
Omitting outputSeparator also generates the three-letter abbreviation for the month. If outputSeparator is specified, the numeric value of the month is generated. See also Default Output Format.
Special processing operators
Special processing operators generate only part of a date, or process dates specially, as follows (see also Output Conversion Examples):
AM Administrative month number, based on the first two months of each quarter having four weeks and the third month having five weeks.
AW Administrative week number, which ends on Sunday. For example, 1987 had 53 weeks. The scheme conforms to ISO 8601.
AY Administrative year, based on each week number starting on Sunday.
D Day of the month as a numeric value.
I Converts a date stored in external format into internal format. The date returned depends on the elements and delimiters of the external date to be converted, and on the date format currently in effect (see Default Output Format), as shown in Input Conversion: Notes and Examples.
Note
You can use this as an input/output or pre-processor code.
J Julian day (ddd, 001 to 365 or 001 to 366 for a leap year).
JY Year and julian day (yyddd).
M Month number (1-12).
MA Month name (January - December).
MI Date as two-digit year, followed by month, followed by day, with no separators.
MI4 Date as four-digit year, followed by month, followed by day, with no separators.
Ppicture Date picture (see below).
Q Number of the quarter (1-4).
S Date as year-month-day (with four-digit year, numeric month and hyphen separators).
W Day of the week as a number (1-7: Monday-Sunday).
WA Day of the week in letters (Monday - Sunday).
Y Year (up to four digits, as set by parameter yearFormat).
Default output format
If you do not specify a special processing operator or an output separator, the default output format is two-digit day, space, three-letter month, space, four-digit year.
If you specify just an output separator, the date format defaults either to the US numeric format mmsddsyyyy or to the international numeric format ddsmmsyyyy, depending on the numeric format currently in effect as follows:
- You can set the default for a database by setting the DateFormat database configuration parameter.
- You can change the numeric format for the duration of a logon session with the DATE-FORMAT TCL command.
If you require the abbreviated month name within converted dates to be output in all uppercase, set the UCASEDATES option in your operating environment.
Dates within strings
The inputField parameter allows you to extract a date field from a string that comprises fields separated by a delimiter character, with the following conditions:
- The delimiter cannot be a number, a minus (-) sign or a system delimiter (value, subvalue, attribute, start buffer, or segment mark).
- If yearFormat (number of year digits) is omitted the delimiter cannot be a letter.
- The date must be the last field (or else the string is returned unchanged).
- The number of fields skipped must be a decimal number with a value from 1 to 65533.
Example
If the D code is D%1 and the stored data is ABC%2704, the processor skips one field (ABC) and returns 2704 in output format, as the string 27 MAY 1975.
Date picture
The P special processing operator allows you to customise the appearance of dates. The picture parameter can contain any combination of the following elements:
|
Picture Element |
Description |
Examples |
||
|---|---|---|---|---|
|
Picture |
Data |
Result |
||
|
M{fieldWidth} |
Month number. fieldWidth is one or more digits, specifying the field width; the number is right-aligned within a field of zeros. |
M M5 |
15335 15335 |
|
|
M{fieldWidth}A{[L||T]} |
Month name.
The default is upper case. |
M3AT |
15335 |
|
|
W{fieldWidth} |
Weekday number; 1 (Monday) to 7 (Sunday). fieldWidth is one or more digits, specifying the field width; the number is right-aligned within a field of zeros. |
W W7 |
15335 15335 |
|
|
W{fieldWidth}A{[L||T]} |
Weekday name.
The default is upper case. |
W3AL |
15335 |
fri
|
|
D{fieldWidth}{S{[L||T]}} |
Day of the month.
|
D D3 D7SL |
15335 15335 15335 |
|
|
Y{fieldWidth} |
Year. fieldWidth is one or more digits, specifying the field width; the number is right-aligned within a field of zeros. |
Y Y6 Y2 |
15335 15335 15335 |
|
|
"string" 'string' |
Literal text. |
"Today is " WAT "." |
15335 |
|
Unless enclosed in quotes (literal text), spaces in the date picture are ignored.
Internal format of date
Dates are stored internally as integers representing the days (plus or minus) from the base date "31 December 1967". For example:
|
Date |
Stored Value |
|---|---|
|
30 December 1967 |
-1 |
|
31 December 1967 |
0 |
|
01 January 1968 |
1 |
|
09 April 1968 |
100 |
|
17 December 1987 |
7291 |
Input conversion: notes and examples
If the year is not specified in the sentence, the processor assumes the current year on input conversion. If the last two digits of the year are specified, the processor uses the date window as set by the IDateWindow database configuration parameter or as superseded by an IDATE-WINDOW TCL command. The default date window value is 30, which implies the following conversion:
00-29 = 2000-2029
30-99 = 1930-1999
For input conversion, the following date formats are recognised:
|
Format |
Notes |
Examples |
|---|---|---|
|
DDsMMMsYY{YY} |
|
25 Dec 75, 10-Apr-2002 |
|
DDsMMsYY{YY} |
If international date format set. |
25/12/1975, 10-04-2002 |
|
MMMsDDsYY{YY} |
|
Dec 25 75, Apr-10-2002 |
|
MMsDDsYY{YY} |
If standard date format set. |
12/25/1975, 04-10-2002 |
|
YYDDD |
DDD is day number from beginning of year. |
75359, 02100 |
|
YYMMDD |
|
751225, 020410 |
|
{YY}YYsMMMsDD |
Use S special processing operator and hyphen separator only. |
75-Dec-25, 2002-Apr-10 |
|
{YY}YYsMMsDD |
Use S special processing operator and hyphen separator only. |
75-12-25, 2002-04-10 |
|
MMMsDD |
|
Dec 25, Apr-10 |
|
DDsMMM |
|
25 Dec, 10-Apr |
Note
-
With the exception of S, Special Processing Operators are ignored when performing input conversion (see table above).
-
Most punctuation characters are recognised as separators. Commonly used separators are space, hyphen (-) and slash (/).
-
If the date cannot be converted, no output results.
Output conversion examples
|
D Code |
Internal Value |
Value Returned |
|---|---|---|
|
D |
15335 |
25 Dec 2009 |
|
D/ |
15335 |
25/12/2009 |
|
D- |
15335 |
25-12-2009 |
|
D0 |
15335 |
25 Dec |
|
D2* |
15335 |
25*12*09 |
|
DAM |
15335 |
12 |
|
DAW |
15335 |
52 |
|
DAY |
15335 |
2009 |
|
DD |
15335 |
25 |
|
DI |
28 APR 2001 |
12172 (internal representation of 28 Apr 2001) |
|
DI |
280401 |
22007 (internal representation of 1 Apr 2028) |
|
DJ |
15335 |
359 |
|
DM |
15335 |
12 |
|
DMA |
15335 |
December |
|
DMI |
12172 |
010428 |
|
DMI4 |
12172 |
20010428 |
|
DQ |
15335 |
4 |
|
DS |
12172 |
2001-04-28 |
|
DW |
15335 |
5 |
|
DWA |
15335 |
Friday |
|
DY |
15335 |
2009 |
|
DY2 |
15335 |
09 |
|
DPMAT " " DSL " "Y |
15335 |
December 25th 2009 |
|
D%1 |
ABC%2916 |
25 Dec 1975 |
|
D0%1- |
ABC%2916 |
25-12 |
In the last two examples, the D code extracts the date following a separator embedded in the data.