ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Problem with date and SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with date and SQL

    Hi!

    I have a problem with SQLRPGLE and date.

    I have a file, (name file1) created with DDL, that have a date with iso format YYYY-MM-DD.
    Data Field Buffer Buffer Field Column
    Field Type Length Length Position Usage Heading
    DATUM_F DATE 10 10 1 Both DATUM_F
    Date Format . . . . . . . . . . . . . . . : *ISO
    Default value . . . . . . . . . . . . . . : None
    Coded Character Set Identifier . . . . . : 273

    In a SQLRPGLE program I have this statement:

    ExecSQLselectcount(*) into :Nrad from file1
    where DATUM_F = :PeFM_Datum_F;

    (The PeFM_Datum_F is a field in the DSPF, date field *iso YYYY-MM-DD)

    It work without problem with date between 1940-01-01 and 2039-12-31 but if I try for example 1920-01-01 I have this error:

    Meddelande-ID . . . . : RNQ0114 Allvarlighet . . . . . : 99
    Meddelandetyp . . . . : Frågemeddelande
    Skickat, datum . . . . : 09.02.17 Skickat, tid . . . . . : 23:48:09

    Meddelande . . : The year portion of a Date or Timestamp value is not in
    the correct range (C G D F).
    Cause . . . . . : RPG procedure PROC1 in program LIB01/PROC1 at
    statement 005600 requested a conversion to a Date value, from a Timestamp or
    a Date value, and the year of the source is not in the allowable range of
    the target.
    -- The range for a 2-digit year is 1940-2039.
    -- The range for a 3-digit year is 1900-2899.
    -- The range for a 4-digit year is 0001-9999.
    Recovery . . . : Check the job log for more information on the cause of the
    error and contact the person responsible for program maintenance.
    Possible choices for replying to message . . . . . . . . . . . . . . . :


    I know that a date with only 2-digit have this problem but the date in the DSPF is a *iso date YYYY-MM-DD.
    I dont understand what is the problem!
    Please can you help me?

    Thank you very much!

  • #2
    What value does debug show is in 'PeFM_Datum_F'?
    Tom

    There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

    Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

    Comment


    • #3
      1. A date in an SQL defined table never has an date format. The date is a rolling number starting with Janary 1st 4713 BC (called Scaliger No). Date Formats are only used to make this numeric value readable. When accessin SQL Tables the date format to be used is taken per default from the current environment/job
      In embedded SQL for date fields additional variables are created and the date format for these variables is determined from the OPTION DATFMT in the compile command. The default value for the Option DATFMT in the compile command is *JOB and the job date format normally has a 2 digit year.
      Your RPG program crashes as soon as a date value before 01/01/1940 is moved (under the cover) into these additional date fields.

      For avoiding any problems with date host variables, you need to change the compile option DATFMT from *JOB to *ISO or, wich is the better solution add an SET OPTION Statement with DATFMT = *ISO in your source code before ALL other SQL statements. SET OPTION will override the compile options.

      Exec SQL SET DATFMT=*ISO;

      Birgitta

      Comment


      • #4
        I have this as the first c-spec lines in each of my sqlrpgle programs:

        //-- Set SQL options
        Exec sql
        SET OPTION commit = *none,
        datfmt = *iso;

        Comment


        • #5
          I have a similar problem with ISO format dates > 2039 being rejected when I try to insert in embedded SQL in RPG.

          The date field (defined as *ISO) correctly contains '2039-12-31', but unless I tell the SQL precompiler to use *ISO dates instead of *JOB, the program throws an error.

          I tried to set this as a default in the compiler prompts in RDI, but it doesn't seem to retain it, and reverts back to *JOB.
          I also have "DATFMT(*ISO)" in the RPG header spec, but that doesn't seem to make a difference either.
          I added the SET OPTION as the first line of the calculation specs, but that made no difference.

          Is it necessary to override the precompiler option EVERY time?
          If so, that's REALLY frustrating...
          Poddys Rambles On

          Comment


          • Poddys
            Poddys commented
            Editing a comment
            SCRATCH THE ABOVE!
            The little grey cells have now kicked in and "Exec SQL SET Option DATFMT=*ISO;" does actually work!
            It's really frustrating that you so often manage to fix things only after you have felt it necessary to post a help request...
        Working...
        X