It's very difficult to calculate dates backward in queries. This trick permits us to do it. Example (January-1)=0 is not good.
Often, we have difficulties to manage calculating with dates in the queries. The solution I found is the following. You can also compact the formulas. This solution is given for the current date but, of course, we can do it the same way with any other date area in a file. You shall then use DIGITS and SUBSTR too.
AREA DEFINITION Heading LENGTH DEC
There are 2 areas to be fed: NBY ( the number of years we want to go back) and NBM ( the number of months we want to go back). Remember that NBM and NBY will be combined in order to obtain X (the total number of months to go back).
NBY ex : 3 or 0 Nb of years to go back NBM ex : 5 or 0 Nb of months to go back X NBY*12+NBM Total of months to go back YY year(current(timestamp)) Current year 4 0 MM month(current(timestamp)) Current month 2 0 DD day(current(timestamp)) Current day 2 0 MMTOT YY*12+MM Total YYBIS ((MMTOT-X-1)/12)-0.5 Destination year MMBIS (YY - YYBIS)*12+ MM-X Destination month BDATEISO YYBIS*10000+MMBIS*100+DD New date ISO BDATEUS MMBIS*1000000+DD*10000+YYBIS New date US Example : Current date US format : April 27th, 2001 "3 04272001 ISO format "3 20010427 We want to go back 3 years and 5 months. We feed NBY=3 and NBM=5. We will get: NBY = 3 NBM = 5 X = 41 YY = 2001 MM = 4 DD = 27 MMTOT = 24016 YYBIS = 1997 MMBIS = 11 BDATEISO= 19971127 BDATEUS = 11271997 Date US format : November 27th, 1997 "3 11271997 ISO format "3 19971127 Good luck.