[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

AS/400 format DATE field

Posted on 2009-04-17
15
Medium Priority
?
2,154 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:KALULAX
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24171525

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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24171534
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
 

Author Comment

by:KALULAX
ID: 24171576
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 36

Expert Comment

by:Gary Patterson
ID: 24171582
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
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 24171625
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
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 24171677
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
 
LVL 18

Expert Comment

by:Dave Ford
ID: 24171692

> 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
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 24171936
Now I want coffee.
0
 

Author Comment

by:KALULAX
ID: 24172423
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
 
LVL 13

Assisted Solution

by:_b_h
_b_h earned 400 total points
ID: 24172785
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
 

Author Comment

by:KALULAX
ID: 24172838
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
 
LVL 13

Expert Comment

by:_b_h
ID: 24173052
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
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 1200 total points
ID: 24173053
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
 
LVL 14

Assisted Solution

by:daveslater
daveslater earned 400 total points
ID: 24182489
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
 

Author Closing Comment

by:KALULAX
ID: 31571593
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

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question