RE: SQLRPGLE - current date - 7 days as a numeric field

You need to define a Host-Variable and include the current date - 7 days as a numeric value in this host variable.
You easily can do it with RPG.

LastWeek90 = %Dec(%Date() - %Days(7): 8: 0);

... WHERE Field90 >= :LastWeek90

Even though SQL can easily convert a date value into a numeric date, for performance issues it is better not to calculate it directly within the WHERE conditions.

Convert (real) date into numeric date YYYYMMDD (with SQL)
Dec(Current_Date - 7 days, 9, 0) //Release 7.2 or higher
Dec(VarChar_Format(Current_Timestamp - 7 days, 'YYYYMMDD'), 9, 0) //Release 6.1 or higher

Also if you are working with dynamic SQL, you cannot use hostvariables within the SQL string representing the SQL Statement.
You either have to include the variable value as text or to work with parameter markers.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Booth Martin
Sent: Sonntag, 30. Juni 2019 04:11
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: SQLRPGLE - current date - 7 days as a numeric field

I have a 9/0 field (020190627) in the data that i want to compare >= with today's date-7 days in an SQL WHERE clause.

I have ... WHERE :FIELD90 >= (current date - 7 days)

This is part of wSQLstmt in: exec sql prepare Stmt1 from :wSQLstmt; and it fails with -514.