user3593083 user3593083 - 6 months ago 45
SQL Question

IBM i Date Diff with CYYMMDD - can't use DATE()

(title edited)
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

but I only see a result "++++++++++++++" for whatever reason. I was able to test a few different versions of this and found I can't use DATE anywhere...can anyone suggest an alternative??

Thanks in advance!



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