SQLRPGLE: How to evaluate field value in where condition

Tags:
AS/400
SQLRPGLE
Dear RPG users, Can we evaluate field value in where condition? I am looking for help in below query:
select * from File_name where Date_Field + 2 days = Today_Date
Date_field = 8P 0
Today_date = 8P 0
What I am looking for is the records which have passed 2 days as per today's date.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Just adding 2 like your example will not handle overlaps.

We do date handling like this
We break out the input date, tick it in an ISO format, add the # of days.
Take the out put result and use that for the other date parameter.
Something like this.
D WorkYMD         DS            10    
D  WMM                    6      7    
D  WDD                    9     10    
D  WYY                    3      4    
D  WYYYY                  1      4    

D Date            S             10D   DATFMT(*ISO)  
D DateOut         S             10D   DATFMT(*ISO)  

 **********************************************************           
 * BUILD NEW DATE FOR CALULATING                                  
 **********************************************************           
                    
C                   EVAL      WorkYMD='20'+INYYA+'-'+INMMA+'-'+INDDA  
C                   MOVE      WorkYMD       DATE   

 ****************************************************
 * Calc Date                                     
 ****************************************************
C     Date          ADDDUR    2:*D          DateOut  
C                   MOVE      DateOut       WorkYMD 

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • BigKat
    I created a test file with a packed 8,0 field.  put in a date of 20140819 and used the date, digits, and substr commands to create a true date value, which can then have days added to it.

    you will need to use the date, digits, and substr on both of your date fields, so it will look a little messy, but it will work.  

    select adate,                              
    date(substr(digits(adate),5,2) || '/' ||   
    substr(digits(adate),7,2) || '/' ||        
    substr(digits(adate),1,4)) + 2 days        
         ADATE   Date expression
    20,140,819      08/21/14    
    8,350 pointsBadges:
    report
  • BigKat
    also your query only gets records that ARE 2 days old, passed two days old would be less than today 

    select * from File_name where 
     date(substr(digits(date_field),5,2) || '/' ||   
      substr(digits(date_field),7,2) || '/' ||        
      substr(digits(date_field),1,4)) + 2 days 
     < 
     date(substr(digits(today_date),5,2) || '/' ||   
      substr(digits(today_date),7,2) || '/' ||        
      substr(digits(today_date),1,4))
    8,350 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following