IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
#Power
 View Only
Back to discussions
Expand all | Collapse all

Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

  • 1.  Discrepancy Between Documentation and Internal Date Representation in DB2 for i 7.3

    Posted Tue August 06, 2024 10:57 AM

    Hi everyone,

    I am trying to better understand how DB2 for i version 7.3 internally handles dates. In the SQL Reference documentation, I found the following passage:

    The internal representation of a date is a string of 4 bytes that contains an integer. The integer (called the Scaliger number) represents the date.

    However, when I run a query to convert a date to an integer, I get a result that appears to be the concatenation of the year, month, and day in the format YYYYMMDD. For example:

    CREATE TABLE test_date (col_date DATE);

    INSERT INTO test_date (col_date) VALUES ('2024-08-06');

    SELECT INTEGER(col_date) AS date_as_integer FROM test_date;

    The result of the query is 20240806, which does not seem to correspond to the concept of a "Scaliger number".

    Questions:

    1. Could you clarify how DB2 for i version 7.3 internally represents dates?
    2. What is the correct way to interpret the results obtained through INTEGER(col_date)?

    Thank you in advance for your help!



    ------------------------------
    Agostino Sclauzero
    ------------------------------



Related Content