AS/400 format DATE field

I am in need of a a query deifnition in the AS/400 Define Results Fields that will format an existing field to a date format. The existing field has data representing an activity date and appears as 20,090,417 which I would like to convert in the report output as 04/17/2009.

The expression I used yeilds nothing but + signs in the ouput.
Last Used Date    LAST ACT
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
   ++++++++     20,090,417
 
 Field       Expression                         Column Heading        Len   Dec
 DATE        DATE(MMDTAC)                       Last Used Date

Open in new window

KALULAXAsked:
Who is Participating?
 
Gary PattersonConnect With a Mentor VP Technology / Senior Consultant Commented:
Yeah, 00/00/0000 is not a valid date.  Date fields must contain a valid date.  Query/400 isn't equipped to deal with this.

Either fix your data to contain valid dates, or you can create an SQL view that uses the following logic:

CREATE VIEW mylib/myview AS
   SELECT (columns),  
         CASE WHEN mmdtac=0 THEN date('01/01/0001')
         ELSE date(substring(char(mmdtac),5,2) || '/' ||
                   right(char(mmdtac,2) || '/' ||
                   left(char(mmdtac),4))
         END AS mmdtac2
   FROM MYLIB/MYFILE

Then, if you absolutely must use Query/400, you can use the view you created, and it will have valid dates in the MMDTAC2 column.

- Gary Patterson
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

It's been several years since I last used WrkQry, but this would be SIGNIFICANTLY easier using SQL.

e.g.

For YYYY-MM-DD:
 
select left(char(intDate),4) ||
        '-' ||
       substr(intDate,3,2) ||
       '-' ||
       substr(intDate,5,2)
from   deleteme2
 
For MM/DD/YYYY
 
select substr(intDate,3,2) ||
       '-' ||
       substr(intDate,5,2) ||
       '-' ||
       left(char(intDate),4)
from   deleteme2
 
HTH,
DaveSlash

Open in new window

0
 
Dave FordSoftware Developer / Database AdministratorCommented:
Oops, for the second example, I meant this:
select substr(intDate,3,2) ||
       '/' ||
       substr(intDate,5,2) ||
       '/' ||
       left(char(intDate),4)
from   deleteme2

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
KALULAXAuthor Commented:
I have used the following and it works great on the AS/400, but when I dump the data to Excel, the date is hexidecimal.

Field       Expression                      
DATE1       DIGITS(MMDTAC)                  
                                             
                                             
                                             
DATE        SUBSTR(DATE1,5,2) || '/' ||      
            SUBSTR(DATE1,7,2) || '/' ||      
            SUBSTR(DATE1,1,4)                
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
First you have to use DIGITS to convert the numeric to character, then you can use substring to parse out the date components, and finally use the concatenation operator to stick it all back together with slashes inthe right places.

substring(digits(mmdtac),5,2) || '/' || substring(digits(mmdtac,7,2) || '/' || substring(digits(mmdtac),1,4)

If you can live with yyyy/mm/dd format,. just use an edit word in the report column formatting ('bbbb/bb/bb') (b=blank) instead of all of this.

- Gary Patterson


0
 
Gary PattersonVP Technology / Senior Consultant Commented:
daveslash,

Shouldn't your 3,2 be 5,2 and your 5,2 be 7,2 in the examples above?

yyyy = positions1-4
mm = pos 5-6
dd = pos 7-8

- Gary Patterson
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
If you are dumping to an output file, the best way to make sure dates convert properly isto conver to DATE data type.  In query/400 this means that you have to take you numeric date, convert it into job date format (DSPJOB to see your job date format), then wrap that converted character date in the DATE function:

Assuming *MDY job date format with a job date separator of "/":

date(substring(digits(mmdtac),5,2) || '/' || substring(digits(mmdtac,7,2) || '/' || substring(digits(mmdtac),1,4))

- Gary Patterson
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

> daveslash,
> Shouldn't your 3,2 be 5,2 and your 5,2 be 7,2 in the examples above?

Yes, Gary, you're absolutely right!

Too much lunch ... not enough coffee ...  :-)

-- DaveSlash
0
 
Gary PattersonVP Technology / Senior Consultant Commented:
Now I want coffee.
0
 
KALULAXAuthor Commented:
Gary...The,

There does not appear to be enough room in the query input field to place...date(substring(digits(mmdtac),5,2) || '/' ||... all on one line
0
 
_b_hConnect With a Mentor Commented:
If the expression does not fit in the three lines available, create several result fields and put them together:
MM            substring(digits(mmdtac),5,2)
DD            substring(digits(mmdtac),7,2)
YY             substring(digits(mmdtac),1,4)
MYDATE    date(MM || '/' || DD || '/' || YY)
0
 
KALULAXAuthor Commented:
Thanks
the problem is now on the output of that field when the query is run...the below error message appears and I think it is related to zero value on some of the items in the MYDATE field

ERROR
Column MYDATE contains replacement character +.

OUTPUT
     
MYDATE      LAST ACT
++++++++           0
03/15/05  20,050,315
08/05/04  20,040,805
02/25/06  20,060,225
++++++++           0
++++++++           0
++++++++           0
++++++++           0
++++++++           0
0
 
_b_hCommented:
Let's see if the problem is in the data by printing out what is going into the date function.

Change MYDATE to:
MYDATE    MM || '/' || DD || '/' || YY
and add MYDATE1
MYDATE1 date(mydate)

Include MYDATE and MYDATE1 in your report, and post back!
Barry
0
 
daveslaterConnect With a Mentor Commented:
Hi
Have you thought of a User defined function

here is the code - compile it to UDF0001


you can the use :
select CvtIso2Iso(Mydate) from MYfile

Dave


NoMain                                                                             
*********************************************************************************** 
** Functions to convert numeric dates to dates - used  in SQL                       
*********************************************************************************** 
DCvtDmy2Iso       Pr              d                                                  
D #date                          6  0 Const                                          
DCvtMdy2Iso       Pr              d                                                  
D #date                          6  0 Const                                          
                                                                                      
DCvtYmd2Iso       Pr              d                                                  
D #date                          6  0 Const                                          
                                                                                      
DCvtEur2Iso       Pr              d                                                  
D #date                          8  0 Const                                          
                                                                                     
DCvtIso2Iso       Pr              d                                                  
D #date                          8  0 Const                                          
PCvtDmy2Iso       b                   Export                                              
DCvtDmy2Iso       Pi              d                                                       
D #date                          6  0 Const                                               
                                                                                          
D iso             s               d   DatFmt(*iso)                                        
                                                                                          
 /Free                                                                                    
      Clear Iso ;                                                                         
                                                                                          
        test(de) *dmy #date;                                                              
        if Not %error ;                                                                   
          Iso = %date(#date : *dmy) ;                                                     
        endif ;                                                                           
                                                                                          
       Return Iso ;                                                                       
                                                                                          
 /End-Free                                                                                
P                 e                                                                       
 ***********************************************************************************      
PCvtmdy2Iso       b                   Export                                              
DCvtmdy2Iso       Pi              d                                                     
D #date                          6  0 Const                                             
                                                                                        
D iso             s               d   DatFmt(*iso)                                      
                                                                                        
 /Free                                                                                  
      Clear Iso ;                                                                       
                                                                                        
        test(de) *mdy #date;                                                            
        if Not %error ;                                                                 
          Iso = %date(#date : *mdy) ;                                                   
        endif ;                                                                         
                                                                                        
       Return Iso ;                                                                     
                                                                                        
 /End-Free                                                                              
P                 e                                                                     
 ***********************************************************************************    
PCvtymd2Iso       b                   Export                                            
DCvtymd2Iso       Pi              d                                                     
D #date                          6  0 Const                                            
                                                                                       
D iso             s               d   DatFmt(*iso)                                     
                                                                                       
 /Free                                                                                 
      Clear Iso ;                                                                      
                                                                                       
        test(de) *ymd #date;                                                           
        if Not %error ;                                                                
          Iso = %date(#date : *ymd) ;                                                  
        endif ;                                                                        
                                                                                       
       Return Iso ;                                                                    
                                                                                       
 /End-Free                                                                             
P                 e                                                                    
 ***********************************************************************************   
PCvtEur2Iso       b                   Export                                           
DCvtEur2Iso       Pi              d                                                    
D #date                          8  0 Const                                            
                                                                                                
 D iso             s               d   DatFmt(*iso)                                             
                                                                                                
  /Free                                                                                         
       Clear Iso ;                                                                              
                                                                                                
         test(de) *Eur #date;                                                                   
         if Not %error ;                                                                        
           Iso = %date(#date : *Eur) ;                                                          
         endif ;                                                                                
                                                                                                
        Return Iso ;                                                                            
                                                                                                
  /End-Free                                                                                     
 P                 e                                                                            
  ***********************************************************************************           
 PCvtIso2Iso       b                   Export                                                   
 DCvtIso2Iso       Pi              d                                                            
 D #date                          8  0 Const                                                    
                                                                                                
D iso             s               d   DatFmt(*iso)                                       
                                                                                         
 /Free                                                                                   
      Clear Iso ;                                                                        
                                                                                         
        test(de) *Iso #date;                                                             
        if Not %error ;                                                                  
          Iso = %date(#date : *Iso) ;                                                    
        endif ;                                                                          
                                                                                         
       Return Iso ;                                                                      
                                                                                         
 /End-Free                                                                               
P                 e                                                                      
 
<<<===================================================>>
then to register it - use
 
 
 
H DftActGrp(*No)                                                                         
H ActGrp(*Caller)                                                                        
H/Copy Hspec                                                                             
                                                                                         
 /Free                                                                                   
                                                                                         
      *inlr = *on ;                                                                      
                                                                                         
      exec sql                                                                           
      Drop  function CvtDmy2Iso ;                                                        
      exec sql                                                                           
      Drop  function CvtYmd2Iso ;                                                        
      exec sql                                                                           
      Drop  function Cvtmdy2Iso ;                                                        
      exec sql                                                                           
      Drop  function CvtEur2Iso ;                                                        
      exec sql                                                                           
      Drop  function CvtIso2Iso ;                                                        
 
      exec sql                                                            
      create function CvtDmy2Iso                                          
      (dec (6,0) )                                                        
      returns date                                                        
      language rpgle                                                      
       deterministic                                                      
       no sql                                                             
       returns null on null input                                         
       simple call                                                        
       external name 'QGPL/UDF0001(CVTDMY2ISO)' ;                         
                                                                          
      exec sql                                                            
      create function CvtYMD2Iso                                          
      (dec (6,0) )                                                        
      returns date                                                        
      language rpgle                                                      
       deterministic                                                      
       no sql                                                             
       returns null on null input                                         
       simple call                                                        
       external name 'QGPL/UDF0001(CVTYMD2ISO)' ;                             
                                                                              
      exec sql                                                                
      create function CvtMDY2Iso                                              
      (dec (6,0) )                                                            
      returns date                                                            
      language rpgle                                                          
       deterministic                                                          
       no sql                                                                 
       returns null on null input                                             
       simple call                                                            
       external name 'QGPL/UDF0001(CVTMDY2ISO)' ;                             
                                                                              
      exec sql                                                                
      create function CvtEUR2Iso                                              
      (dec (8,0) )                                                            
      returns date                                                            
      language rpgle                                                          
       deterministic                                                          
       no sql                                                                 
       returns null on null input                                                        
       simple call                                                                       
       external name 'QGPL/UDF0001(CVTEUR2ISO)' ;                                        
                                                                                         
      exec sql                                                                           
      create function CvtISO2Iso                                                         
      (dec (8,0) )                                                                       
      returns date                                                                       
      language rpgle                                                                     
       deterministic                                                                     
       no sql                                                                            
       returns null on null input                                                        
       simple call                                                                       
       external name 'QGPL/UDF0001(CVTISO2ISO)' ;                                        
                                                                                         
                                                                                         
 /End-Free                                                                               

Open in new window

0
 
KALULAXAuthor Commented:
Hey Guys, thanks for your help on this, however, because of SOX, I do not have access to the UDF so I will need to turn over to our programming group. I was able to dump the raw data into Excel and clean up the date issue much more easily. This will have to work for meantime. But many of your other insights will come in handy down the road on other queries.

KALULAX
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.