• If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Announcement

Collapse
No announcement yet.

Dates store as numeric fields in DB2

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Dates store as numeric fields in DB2

    Is it easier or better to transform a date stored as number in DB2 in the RPG Valence SQL back end when the data is loaded or it easier or better to render it in Sencha Architect?

    For example:
    Some dates store as 6,0 (mmddyy)
    Some dates stored as 7.0 (cyymmdd)

    Is it better to format the on the backend (digits to characters and substrings) during loading of the data or is it better/easier on the Sencha Architect front end to format it?

    I want to build a date cheat sheet reference will be used for standards when developing.

    Thanks in advance for any help.

  • #2
    Here is an answer to my question. I spoke with CNX Corp and they said that "best practice" is to convert numeric dates to an ISO date on the backend RPG. To keep your SQL clean use a procedure similar to below to convert the numeric date to string that you can use/reuse in your SQL.

    Example: Numeric date stored in CYMD format to ISO date format:

    Code:
    *D-specifications
    d cymdToIso       pr           512a   varying  
    d  fieldName                    10a   const   
    
     *------------------------------------------------------------------------------
     // cymdToIso - Procedure to convert number (CYYMMDD) to ISO date (YYYY-MM-DD)  
    p cymdToIso       b                                                             
    d                 pi           512a   varying                                   
    d  fieldName                    10a   const                                     
     /free                                                                          
      return ' '+sq+'20'+sq+'||substr(digits('+ %trim(fieldName)+ '),2,2)||'        
             + sq                                                                   
             + '-'                                                                  
             + sq                                                                   
             + '||substr(digits(' + %trim(fieldName) + '),4,2)||'                   
             + sq                                                                   
             + '-'                                                                  
             + sq                                                                   
             + '||substr(digits(' + %trim(fieldName) + '),6,2) ';                   
     /end-free                                                                      
    p                 e       
    
    In your SQL Statement use this.
    
    stmt = 'select  MYFIELD1, MYFIELD2, MYFIELD3, +
               cymdToIso('MYCYMD')date + ' as MYDATE' +
               ' from MYFILE';

    Comment

    Working...
    X