Representation of Date/Time Values

Data Types and Null Values › Representation of Date/Time Values

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.

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.

Note: 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.

Note: For more information about precompiler options, see the CA IDMS SQL Programming Guide.