Representation of Date/Time Values

Values whose data types are DATE, TIME, or TIMESTAMP are represented internally as binary numbers and externally as character strings.
idmscu
Values whose data types are DATE, TIME, or TIMESTAMP are represented internally as binary numbers and externally as character strings.
2
2
Internal Representation
Date/time values are represented internally by 64-bit binary numbers. The bits, numbered from the left starting with 0, have the following meaning:
Bits
Meaning
Bits 0 through 26
Number of days since January 1, 0001
Bits 27 through 43
Number of seconds in the day since midnight
Bits 44 through 63
Number of microseconds since the last second
External Representations
Date/time Representations
CA IDMS provides four standard external representations for a date/time value:
  • International Standards Organization (ISO)
  • IBM USA standard (USA)
  • IBM European standard (EUR)
  • Japanese Industrial Standard Christian Era (JIS)
You can reference the abbreviation associated with the external representation (for example, ISO) when you use the CHAR function.
For more information about the CHAR function, see CA IDMS Scalar Functions.
Date Values
The external representation of a date value is a character string which begins with a digit and must be at least five characters. The representation of a date value can:
  • Omit leading zeros from the month, day, and year portions
  • Include trailing blanks
The following table describes the external representations of a date value:
Standard
Format
Example
ISO
yyyy
-
mm
-
dd
1990-12-15
USA
mm
/
dd
/
yyyy
12/15/1990
EUR
dd
.
mm
.
yyyy
15.12.1990
JIS
yyyy
-
mm
-
dd
1990-12-15
External Representations of Time Values
The external representation of a time value is a character string which begins with a digit and must be at least five characters. The representation of a time value may omit a leading zero from the hours, minutes, and seconds portions and may include trailing blanks.
The following table describes the external representations of a time value:
Standard
Format
Example
ISO
hh
.
mm
.
ss
16.43.17
USA
hh
:
mm
AM
hh
:
mm
PM
4:43 PM
EUR
hh
.
mm
.
ss
16.43.17
JIS
hh
:
mm
:
ss
16:43:17
External Representations of Timestamps
The external representation of a timestamp is a character string which begins with a digit and must be at least 11 characters. The timestamp format is:
yyyy
-
mm
-
dd
-
hh
.
mm
.
ss
.
nnnnnn
With the representation of a timestamp value may:
  • Omit leading zeros from the year, month, day, hours, minutes, and second portions
  • Truncate or omit microseconds entirely (any digit of microseconds that is omitted is assumed to be zero)
  • Include trailing blanks
Entering and Retrieving Date/Time Values
When you enter date/time values into the database or use them in predicates, you can use any of the formats documented above. CA IDMS determines the format by examining the value.
When you retrieve date/time values from the database, the format in which the value is retrieved depends on how the SQL statement is issued:
  • If the statement is embedded in a program, the format is determined by the DATE and TIME precompiler options, if specified, or by the installation default.
  • If the statement is submitted through the Command Facility, all date/time values are returned in ISO format.
For more information about precompiler options, see Precompiling the Program.