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.
Announcement
Collapse
No announcement yet.
Add months to a numeric date field
Collapse
X
-
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
Code:cast( varchar_format( timestamp_format( digits( cast(DATE1 as numeric(8)) ) ,'yyyymmdd' ) + 999 months ,'yyyymmdd' ) as numeric(8) )
- 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)
-
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
Comment