Good afternoon, all!
Using IBM i version 7.1 and looking to calculate difference between two dates in a query. Since nothing is ever easy, one date is in CYYMMDD format, the other (curdate()) is YYYY-MM-DD. I tried to CAST my CYYMMDD formatted date (field name APENGD) as a varchar(10) then wrapped that in a CAST as a date (since decimals can't be CASTed as dates):
Cast(Cast(APENGD + 19000000 As varchar(10)) As date) As math
casting varchar to date only works when the string includes separators.
At 7.1 you could use
TIMESTAMP_FORMAT(), but you'd end up with a timestamp instead of just a date. But that's easily dealt with.
Date(Timestamp_format(char(APENGD + 19000000),'YYYYMMDD')) As math
My prefered solution when dealing with numeric/character value dates is creating a User Defined Function to handle conversion.
You could write your own, or use the one I do. iDate written by Alan Campin. Then your code would simple be:
idate(APENGD,'*CYMD') as nath
Note that if you're trying to use date differences in a
WHERE clause, like so
WHERE CURRENT_DATE - 3 months <= idate(APENGD,'*CYMD')
The above will perform poorly since an existing index over APENGD can't be used (directly). Assuming a recent(6.1+) version of the OS, you can create a new index that includes the expression you're using to convert APENGD to date.
Or you could code it using the Date->Numeric function
ConvertToIdate that Alan helpfully includes. That would allow existing indexes to be used.
WHERE ConvertToiDate(CURRENT_DATE - 3 months,'*CYMD') <= APENGD