ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Add months to a numeric date field

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

  • Add months to a numeric date field

    I'm not how to do this in SQL or if I should just write it in RPG: I have to read a file that has two numeric dates in YYYYMMDD format and select those records where DATE1 plus 999 months = DATE2. Once I have the list and it is approved by the data owner, I have to change the DATE2 records to have a value of 99999999. I presume the second part will be easy once I have the SQL statement to derive the list.

  • #2
    SQL can take a date/timestamp data type and add/subtract a number of days, weeks, or months to it.

    So your steps are:
    • Convert your numeric date DATE1 to a timestamp
    • Add 999 months to it
    • Convert it back to a numeric for comparison with DATE2
    You can do that with this:

    Code:
    cast(
        varchar_format(
            timestamp_format(
                digits(
                    cast(DATE1 as numeric(8))
                )
                ,'yyyymmdd'
            )
            + 999 months
            ,'yyyymmdd'
        ) as numeric(8)
    )
    Let's break this down
    • cast(DATE1 as numeric(8)) - this converts DATE1 to an 8 digit numeric with 0 dp (this is only necessary if it's not already an 8 digit numeric with 0 dp)
    • digits() - convert it to a string
    • timestamp_format(***,'yyyymmdd') convert the yyyymmdd date string to a timestamp
    • + 999 months - add 999 months to the timestamp
    • varchar_format(***,'yyyymmdd') - convert the timestamp to a yyyymmdd format date string
    • cast(***, as numeric(8)) - convert the yyyymmdd date string to a numeric

    You can then compare this value directly against DATE2 (assuming that DATE2 is a numeric type large enough to hold the new date in yyyymmdd format)

    Comment


    • Vectorspace
      Vectorspace commented
      Editing a comment
      FYI - the reason for using digits() is it preserves leading zero's. Not relevant in this case since I highly doubt you will be using dates before the year 1000, but in case anyone else tries to use this method for date formats ddmmyyyy or mmddyyyy

  • #3
    Here is an easiser way (but it only works for numeric dates within the forma YYYYMMDD:
    Code:
    Dec(Date(Digits(YourNumDate) concat '000000') + 999 Month, 8, 0)
    Nevertheless, you need to convert twice!

    Birgitta

    Comment


    • #4
      Birgitta's solution yields the following error:

      Message: [SQL0402] DECIMAL use not valid. Cause . . . . . : An operand has been specified for the arithmetic function or operator DECIMAL that is not valid. -- User-defined types cannot be specified as operands of operators or scalar functions. User-defined types can only be specified with operators and within user-defined functions created specifically for that type. -- The operand of DIGITS can be any numeric or numeric compatible type except floating-point or decimal floating-point. -- The operand of INTEGER, SMALLINT, BIGINT, DECIMAL, ZONED, FLOAT, REAL, DOUBLE or DOUBLE_PRECISION, and DECFLOAT cannot be date, time, or timestamp. -- The other functions or operators require numeric or numeric compatible operands. Recovery . . . : Ensure all operands of function or operator DECIMAL are valid. Correct the operands. Try the request again.

      Comment


      • #5
        Can you provide the whole SQL statement, and confirm the data types and sizes of the DATE1 and DATE2 columns?

        And since they are not actual date data types, it's theoretically possible that not all of them contain real dates. Can you confirm that there are no records with invalid values?

        Comment

        Working...
        X