This document describes each of the possible date-time edit masks and provides processing and conversion information for them. It covers the following topics:
Fields with the E(DATE) format are composed of eight digits in YYYYMMDD format, where YYYY can range from "0001" through "9999", MM can range from "01" through "12", and DD can range from "01" through "31". DATE must be valid according to the Gregorian calendar -- as long as there is no support for other calendars. This means that February 29th (Leap day) is only valid on Leap years (years divisible by four except when divisible by 100 but not divisible by 400).
Historically, the Gregorian calendar started to replace the Julian calendar on October 15, 1582 (the cutover date). When DATE format is used, earlier dates than October 15, 1582 are handled according to the same Gregorian calendar rules (as if the Gregorian calendar was in effect before the cutover date).
Negative values are not allowed and empty values are represented as zero.
| Formats | Dec. 31, 2008 | Empty Value | Jan 1,1 (minimum) | Dec. 31, 9999 (maximum) |
|---|---|---|---|---|
| U.8 | Z'20081231' | Z'00000000' | Z'00010101' | Z'99991231' |
| P,5 | X'020081231F' | X'000000000F' | X'000010101F' | X'099991231F' |
| B,4 and F,4 | F'20081231' | F'0' | F'10101' | F'99991231' |
Unpacked format is the concatenation of year, month, and day, written as character digits. This is also called "zoned form" (e.g., '0' is X'F0' in EBCDIC or X'30' in ASCII).
Packed data is the unpacked value packed.
Binary or fixed point format is calculated as: year*10000 +
month*100 + day*1.
Fields with the E(TIME) format are composed of six digits in HHMMSS format, where HH can range from "00" through "23" and MM and SS can each range from "00" through "59". Leap seconds (SS=60) are not supported. Negative times are not allowed.
| Formats | 13:20:59 (sample) | Empty Value (default) | 00:00:00 (minimum) | 23:59:59 (maximum) |
|---|---|---|---|---|
| U.6 | Z'132059' | Z'000000' | Z'000000' | Z'235959' |
| P,4 | X'0132059F' | X'0000000F' | X'0000000F' | X'0235959F' |
| B,3 and F,4 | F'132059' | F'0' | F'0' | F'235959' |
Unpacked format is the concatenation of hours + minutes + seconds
Packed data is the unpacked value packed.
Binary or fixed point format is calculated as the binary number of:
hour*10000 + minutes*100 + seconds*1.
Fields with the E(DATETIME) format are composed of eight digits of DATE in YYYYMMDD format and six digits of TIME in HHMMSS format. The combined rules of E(DATE) format and E(TIME) format apply.
| Formats | Dec. 31, 2008 13:20:59 (sample) | Empty Value (default) | Jan 1,1 00:00:00(minimum) | Dec. 31, 9999 23:59:59 (maximum) |
|---|---|---|---|---|
| U.14 | Z'20081231132059' | Z'00000000000000' | Z'00010101000000' | Z'99991231235959' |
| P,8 | X'020081231132059F' | X'000000000000000F' | X'000010101000000F' | X'099991231235959F' |
| B,4 and F,4 | F'20081231132059' | F'0' | F'10101000000' | F'99991231235959' |
Fields with the E(TIMESTAMP) format are composed from the 14 digits of DATETIME and the six digits of microseconds.
| Formats | Dec. 31, 2008 13:20:59 123456
(sample) |
Empty Value (default) | Jan 1,1 00:00:00 000 000
(minimum) |
Dec. 31, 9999 23:59:59 999 999
(maximum) |
|---|---|---|---|---|
| U.20 | Z'20081231132059123456' | Z'00000000000000000000' | Z'00010101000000000000' | Z'99991231235959999999' |
| P,11 | X'020081231132059123456F' | X'000000000000000000000F' | X'000010101000000000000F' | X'099991231235959000000F' |
Fields with the E(NATDATE) format contain the number of days since January 2 of year 0. January 1, 0001 is equivalent to NATDATE(0) + 365.
The following information about dates in Natural should be considered:
A RESET on a D field returns a blank or 0 (zero) if it is redefined as P7.
The lowest date that can be displayed is D'1582-0101'. This is the year when the Gregorian calendar was first introduced.
The maximum year that can be used in a Natural session is governed by the Natural profile parameter MAXYEAR. The default is 2699. Alternatively, it can be set to 9999.
In Adabas, we allows NATDATE values between 1582-01-01 and 9999-12-31.
The proleptic Gregorian calendar (produced by extending the Gregorian calendar backward to dates preceding its official introduction in 1582) is used as the basis for converting NATDATE to year, month, day. For complete information, read Conversions Between Date-Time Representations.
| Formats | Dec. 31, 2008
(sample) |
Empty Value (default) | Jan 1,1
(minimum) |
Dec. 31, 9999
(maximum) |
|---|---|---|---|---|
| P,4 | X'0733771F' | X'0000000F' after RESET | Invalid | X'3652423F' |
| U,7 | Z'0733771' | Z'0000000' | Invalid | Z'3652423' |
| B,3 and F,4 | X'03B7F9' | X'00000000' | Invalid | X'37BB47' |
Here are some sample dates:
| Sample Dates | Value |
|---|---|
| 0001-01-01 (outside range) | 365 |
| 1582-01-01 (minimum) | 577813 |
| 1970-01-01 | 719527 |
| 2699-12-31 (MAXYEAR default) | 985789 |
| 9999-12-31 (MAXYEAR=9999) | 3652423 |
Fields with the E(NATTIME) format contain the number of days since January 2 of year 0 multiplied by 86400 * 10 plus the number of 0.1 seconds in a day.
A RESET on a T field returns January 2 of year 0. This is 365 days before the DATE epoch 0001-01-01.
The lowest date and time which can be set as a non-zero value is E'1582-01-01 00:00:00.0'. This is the year when the Gregorian calendar was first introduced.
In Natural, the profile parameter MAXYEAR specifies what the maximum year allowed in a Natural session. The default is 2599, but it can be set to 9999.
Adabas allows values between 1582-01-01 and 9999-12-31.
| Formats | Dec. 31, 2008 13:20:59
(sample) |
Empty Value (default) | Jan 1,1
(minimum) |
Dec. 31, 9999
(maximum) |
|---|---|---|---|---|
| P,7 | X'06339786 24591F' |
X'00000000 00000F' after RESET |
Invalid | X'31556943 35999F' |
| U,13 | Z'6339786 24591' |
Z'000000 0000000' |
Invalid | Z'3155694 335999' |
| B,6 and F,8 | X'939C121A4F' | X'000000000000' | Invalid | X'02DEBE 0A37FF' |
Here are some sample dates:
| Sample Dates | Value |
|---|---|
| 0001-01-01 00:00:00.0 (outside range) | 315360000 |
| 1582-01-01 00:00:00.0 (minimum) | 499230432000 |
| 1970-01-01 00:00:00.0 | 621671328000 |
| 2699-12-31 23:59:59.9 (MAXYEAR default) | 852037055999 |
| 9999-12-31 23:59:59.9 (MAXYEAR=9999) | 3155694335999 |
Fields with the E(UNIXTIME) format contain the number of seconds since January 1, 1970.
Normally UNIXTIME is UTC-based and conversion to local time is performed by the user application. When using it with local time values, Adabas will do no timezone adjustments or validity checking.
The maximum value of the 32-bit integer is 2147483647 or X'7FFFFFFF', which is equivalent to 2038-01-19 03:14:07.
Adabas will allow negative values if the underlying format is not B. Then, for a fixpoint field of length 4, the earliest date and time are 1901-12-13 20:45:52 (-2147483648 seconds or X'80000000').
Using 64-bit integers (format F, length 8) allows the full range of 0001-01-01 to 9999-12-31.
Using binary format B, 4, the range is from 0 (representing 1970-01-01 00:00:00) to a maximum value of X'FFFFFFFF' (representing 2106-02-06 06:28:15).
| Formats | Dec. 31, 2008 13:20:59
(sample) |
Empty Value (default) | Jan 1,1
(minimum) |
Dec. 31, 9999
(maximum) |
|---|---|---|---|---|
| F4, B,4 | 1230729659 | 0 | Invalid -- value truncation | Invalid -- value truncation |
| F,8 | 1230729659 | 0 | -62135596800 | 253402300799 |
| B,5 | X'00495B71BB' | 0 | Invalid -- negative value | X'3AFFF3317F' |
| P,7 | X'01230729659F' | X'00000000000F' | -62135596800 | X'0253402300799F' |
| U,12 | Z'1230729659' | Z'0000000000' | Z'--62135596800' | 253402300799 |
Fields with the E(XTIMESTAMP) format contain the number of microseconds since January 1, 1970.
Normally UNIXTIME is UTC-based and conversion to local time is performed by the user application. When using it with local time values, Adabas will do no timezone adjustments or validity checking.
Adabas will allow negative values if the underlying format is not B.
| Formats | Dec. 31, 2008 13:20:59
(sample) |
Empty Value (default) | Jan 1,1
(minimum) |
Dec. 31, 9999
(maximum) |
|---|---|---|---|---|
| F,8 | 1230729659 123456 |
0 | -62135596800 000000 |
253402300799 999999 |
| B,8 | X'045F57 94258700' |
0 | invalid | X'0384440C CC735FFF' |
| P,10 | X'000123072965 9123456F' |
X'000000000000 0000000F' |
X'62135596800 000000D' |
X'0253402300799 999999F' |
| U,18 | Z'1230729659 123456' |
Z'0' | Z'--62135596800 000000' |
Z'253402300799 999999' |
The following table describes how non-zero, non-null date-time data is converted between date-time formats. Conversion rule letters are described at the end of the table. For information on the zero and null data conversions, read Default and Null Value Handling.
| Input Format | Output Format | |||||||
|---|---|---|---|---|---|---|---|---|
| DATE | TIME | DATETIME | TIMESTAMP | NATDATE | NATTIME | UNIXTIME | XTIMESTAMP | |
| DATE | Allowed. No date-time conversion is required for read or update commands. | n/a | Allowed. Time portion
will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Time and
microseconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. . |
Allowed. Conversion is required and uses rule C for both read and update commands. Date-time format limits are checked. | Allowed. Time values
and tenths of seconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Time will be
zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Time values
and microseconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
| TIME | n/a | Allowed. No date-time conversion is required for read or update commands. | n/a | n/a | n/a | n/a | n/a | n/a |
| DATETIME | Allowed. Time will be
omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. The date will
be omitted.
Uses conversion rule X for read commands. Conversion is not allowed for update commands. |
Allowed. No date-time conversion is required for read or update commands. | Allowed. Microseconds
will be zeros.
Uses conversion rule F for read commands and rule T for update commands . |
Allowed. Time will be
omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. The tenths of
a seconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Conversion is required and uses rule C for both read and update commands. | Allowed. Microseconds
will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
| TIMESTAMP | Allowed. Time and
microseconds will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. The date will
be omitted.
Uses conversion rule X for read commands. Conversion is not allowed for update commands. |
Allowed. Microseconds
will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. No date-time conversion is required for read or update commands. | Allowed. Time and
microseconds will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Values below
the tenths of a second resolution will be zero.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Microseconds
will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Conversion is required and uses rule C for both read and update commands. |
| NATDATE | Allowed. Conversion is required and uses rule C for both read and update commands. | n/a | Allowed. The time will
be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Time and
microseconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. No date-time conversion is required for read or update commands. | Allowed. Time and
tenths of seconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Time will be
zero (rounded to the start of the day value).
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Time and
microseconds will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
| NATTIME | Allowed. Time and
tenths of seconds will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. The date will
be omitted.
Uses conversion rule X for read commands. Conversion is not allowed for update commands. |
Allowed. The tenths of
seconds will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Values below
the tenths of a second resolution will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. The time and
tenths of a second will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. No date-time conversion is required for read or update commands. | Allowed. The tenths of
a second will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Values below
the tenths of a second resolution will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
| UNIXTIME | Allowed. Seconds in the
day (time) will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. The date will
be omitted.
Uses conversion rule X for read commands. Conversion is not allowed for update commands. |
Allowed. Conversion is required and uses rule C for both read and update commands. | Allowed. Microseconds
will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
Allowed. Seconds in the
day (time) will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Uses conversion rule F for read commands and rule T for update commands. | Allowed. No date-time conversion is required for read or update commands. | Allowed. Microseconds
will be zero.
Uses conversion rule F for read commands and rule T for update commands. |
| XTIMESTAMP | Allowed. Microseconds
in the day (time) will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. The date will
be omitted.
Uses conversion rule X for read commands. Conversion is not allowed for update commands. |
Allowed. Microseconds
in the day (time) will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Conversion is required and uses rule C for both read and update commands. | Allowed. Microseconds
in the day (time) will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Values below
the tenths of a second resolution will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. Microseconds
will be omitted.
Uses conversion rule T for read commands and rule F for update commands. |
Allowed. No date-time conversion is required for read or update commands. |
Conversion rule letters have the following meanings:
| Rule Letter | Meaning |
|---|---|
| C | The source and target value have the same precision. |
| F | Fill up the value with zeros; the target value has the higher precision. Date-time format conversion may be required depending on the date-time edit mask in use. |
| T | Truncate the value; the target value has the lower precision. Date-time format conversion may be required depending on the date-time edit mask in use. |
| X | Extract the time component from the value; the target value does not contain date information. Date-time format conversion may be required, depending on the date-time edit mask in use. |
The following table describes how non-zero, non-null date-time data is converted between date-time formats for fields with the TZ option. For information on the zero and null data conversions, read Default and Null Value Handling.
| Input Format | Output Format | |||||||
|---|---|---|---|---|---|---|---|---|
| DATE | TIME | DATETIME | TIMESTAMP | NATDATE | NATTIME | UNIXTIME | XTIMESTAMP | |
| DATETIME | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | UTC times are not converted according to timezone for either read or update operations. | UTC times are not converted according to timezone for either read or update operations. |
| TIMESTAMP | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | UTC times are not converted according to timezone for either read or update operations. | UTC times are not converted according to timezone for either read or update operations. |
| NATTIME | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | UTC times are not converted according to timezone for either read or update operations. | UTC times are not converted according to timezone for either read or update operations. |
| UNIXTIME | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | UTC times are not converted according to timezone for either read or update operations. | UTC times are not converted according to timezone for either read or update operations. |
| XTIMESTAMP | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | Date-time conversions
from UTC to local time are allowed in read operations.
No date-time conversions are allowed in update operations. |
Date-time conversions from UTC to local time and from local time to UTC are allowed for both read and update operations. | UTC times are not converted according to timezone for either read or update operations. | UTC times are not converted according to timezone for either read or update operations. |
Depending on the numerical format of the field, format 0 is returned for empty values.
For DATE, DATETIME, TIMESTAMP, NATDATE, and NATTIME edit masks, empty values are distinct from a valid date-time value.
For TIME, UNIXTIME, and XTIMESTAMP edit masks, empty values are equal to the epoch of that format. In other words, for TIME format, the epoch is 00:00:00; for UNIXTIME or XTIMESTAMP formats, the epoch is January 1, 1970 00:00:00.
With these formats, a proper value cannot be distinguished from a null value unless the NC option is specified for the field in the FDT.
For most date-time fields, a zero default value will be converted to zero in the target date-time format. However, be careful when processing zero default values using UNIXTIME or XTIMESTAMP formats. Conversion of a zero default value from UNIXTIME or XTIMESTAMP to other date-time formats (except TIME) will result in a value equivalent to the date 1970-01-01, rather than 0 (zero). This can lead to the filling up of empty fields. For example, consider a situation where a DATETIME field defined in the FDT is performing reads and updates using the UNIXTIME format, effectively not changing the zero value in the record buffer. An update would convert and store this value as the date 1970-01-01, which is not the default value 0 (zero) in DATETIME format. In addition, displaying these date-time values would appear as 1970-01-01 instead of 0 (zero).
The following table depicts the asymmetry of zero default value conversions between date-time formats.
| Input Format | Converted Zero Output Format | |||||||
|---|---|---|---|---|---|---|---|---|
| DATE | TIME | DATETIME | TIMESTAMP | NATDATE | NATTIME | UNIXTIME | XTIMESTAMP | |
| DATE | 0 | n/a | 0 | 0 | 0 | 0 | 0 | 0 |
| TIME | n/a | 0 | n/a | n/a | n/a | n/a | n/a | n/a |
| DATETIME | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| TIMESTAMP | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| NATDATE | 0 | n/a | 0 | 0 | 0 | 0 | 0 | 0 |
| NATTIME | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| UNIXTIME | 1970-01-01 | 0 | 1970-01-01 | 1970-01-01 | 1970-01-01 | 1970-01-01 | 0 | 0 |
| XTIMESTAMP | 1970-01-01 | 0 | 1970-01-01 | 1970-01-01 | 1970-01-01 | 1970-01-01 | 0 | 0 |
The following value verification processing is performed on date-time field values:
Unless the field value is zero, date-time field values in DATE, TIME, DATETIME, or TIMESTAMP formats are always checked for valid components. For example, they are checked to determine that any year values lie in the range 1-9999.
Date-time fields in NATDATE, NATTIME, UNIXTIME, or XTIMESTAMP formats are checked to ensure they do not exceed the equivalent TIMESTAMP maximum value of "9999-12-31 23:59:59.999999".
Date-time fields in NATDATE and NATTIME formats may be zero. Otherwise, they must greater than or equal to the value 1582-01-01.
Date-time fields in UNIXTIME and XTIMESTAMP formats are checked to ensure they are not smaller than the TIMESTAMP minimum value of "0001-01-01 00:00:00.000000".
Date-time field values must fit into the target fields; they are checked to ensure they do not exceed the target field lengths.
