Problem solve Get help with specific problems with your technologies, process and projects.

Calculating dates by query

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.


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. 

Dig Deeper on RPG iSeries programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.