Date-Time Edit Mask Reference

This document describes each of the possible date-time edit masks and provides processing and conversion information for them. It covers the following topics:


E(DATE) Edit Mask

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.

E(TIME) Edit Mask

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.

E(DATETIME) Edit Mask

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'

E(TIMESTAMP) Edit Mask

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'

E(NATDATE) Edit Mask

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

E(NATTIME) Edit Mask

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

E(UNIXTIME) Edit Mask

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

E(XTIMESTAMP) Edit Mask

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'

Conversions Between Date-Time Representations

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.

Conversions Between Date-Time Representations for Fields with the TZ option

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.

Default and Null Value Handling

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

Value Verification

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.