IBM has added a great deal of functionality in the area of date and time handling to the RPG language in recent years. These features were added gradually over time and seemingly without a master strategy from IBM. This results in confusion in the RPG community over best practises and a wide variety of solutions for a small number of problems. You may find great variation of coding techniques used to handle date and time issues.

Here are some notes and experiments with recent date features in RPGIV and DDS

Defining Dates and Times in DDS


Below is an example of a DDS source member with data types DATE TIME and DATESTAMP

A          R EMPLOYEE                               
A            EMPLOYEE_N     6  0                    
A            NAME_F        25A                      
A            NAME_L        25A                      
A            BIRTH_DATE      L                      
A            BIRTH_TIME      T                      
A            TIMESTAMP       Z                      


For those who prefer creating their AS/400 physical files with an SQL statement (didn't know you could do this, eh?), it would look something like this:

 
CREATE TABLE mylib/MYFILE 
( EMPLOYEE_NO INT NOT NULL WITH DEFAULT,     
  NAME_F CHAR ( 25) NOT NULL WITH DEFAULT, 
  NAME_L CHAR ( 25) NOT NULL WITH DEFAULT, 
  BIRTH_D DATE NOT NULL WITH DEFAULT, 
  BIRTH_T TIME NOT NULL WITH DEFAULT, 
  TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT )           


If you don't populate a date field it will contain the lowest acceptable value of a date field. Different applications may interpret date data differently.  If you look at an un-populated record in DFU, it will look like this.


EMPLOYEE_N:                                  
NAME_F:                                      
NAME_L:                                      
BIRTH_DATE: 0001-01-01                       
BIRTH_TIME: 00.00.00                         
TIMESTAMP:  0001-01-01-00.00.00.000000       		  
		  

Note that SQL/400 is not as informative:


BIRTH_DATE  BIRTH_TIME  TIMESTAMP                       
 ++++++++    00:00:00   0001-01-01-00.00.00.000000      
********  End of data  ********                         	

AS/400 date fields are stored internally as a 4 byte integer representing a number of days from a given base date.  This is called the super-julian method by some.  However, whenever the date is to be displayed, printed, written to a file, etc, the date is converted from an integer to the specified format. 0001-01-01 is the smallest date you can enter into a date field.  If you try to enter 0000-00-00, the operating system will toss you an error message.

Note that some ODBC drivers have problems with date 0001-01-01. If you are working in a client server environment, you may have to populate date fields with 1950-01-01 or something like this to represent an unknown or no date.

The default usage of the date type is *ISO, which has a field length of 10 and is displayed in YYYY-MM-DD format , but you can override that using the DATFMT keyword in the DDS, in the RPG control specification (H-spec) or at the D-spec when defining a date. A program or file can have many dates defined, all with different formats.  


A            DATE1           L         DATFMT(*MDY)
A            DATE2           L         DATFMT(*MDY)
A            DATE3           L         DATFMT(*DMY)
A            DATE4           L         DATFMT(*YMD)
A            DATE5           L         DATFMT(*JUL)
A            DATE6           L         DATFMT(*ISO)
A            DATE7           L         DATFMT(*USA)
A            DATE8           L         DATFMT(*EUR)
A            DATE9           L         DATFMT(*JIS)

If you look at the fields defined above, in an unpopulated record in DFU, it will show the values below.  Notice the different default values and separators used.  Note that the date formats that display the year in a two digit format (like *YMD and *JUL) have a valid range of January 1, 1940 through December 31, 2039.

DATE1: 01/01/40
DATE2: 01/01/40
DATE3: 01/01/40
DATE4: 40/01/01
DATE5: 40/001
DATE6: 0001-01-01
DATE7: 01/01/0001
DATE8: 01.01.0001
DATE9: 0001-01-01



You have a similar range of choices for the new Time data type. The default here is also *ISO

A            TIME0           T
A            TIME1           T         TIMFMT(*HMS)
A            TIME2           T         TIMFMT(*ISO)
A            TIME3           T         TIMFMT(*USA)
A            TIME4           T         TIMFMT(*EUR)




Here I wanted to enter 23:50:59 in each field. The results are pretty predictable.

TIME0: 23.50.59
TIME1: 23:50:59
TIME2: 23.50.59
TIME3: 11:50 PM
TIME4: 23.50.59

 



Defining Dates and Times within your RPG program

Here is an example of defining date, time and timestamp fields in an RPG program.


d* Date1 defaults to *ISO '0001-01-01'                                 
d  Date1          s               d                                    
d*                                                                     
d* Date2 set to initialize with type *USA '01/01/0001'                 
d  Date2          s               d   DATFMT(*USA)                     
d*                                                                     
d* Time1 initialize to value '00.00.00'                                
d  Time1          s               t                                    
d*                                                                     
d* TimeStamp1 initialize to value '0001-01-01-00.00.00.000000'         
d  TimeStamp1     s               z                                    
d*                                                                     

Here is an example of a data structure that allows you to break a date-type field into YYYY MM DD numeric fields. These are very common in older programs:


d                 ds                  Inz
d Date1                   1     10d   DatFmt(*ISO)
d  Year                   1      4  0
d  Month                  6      7  0
d  Day                    9     10  0

In current versions of RPG, the EXTRCT (Extract Date) keyword makes this task simpler and more explicit.

dDate1             s               d   Inz(d'2002-01-30')
 
C                   Extrct    Date1:*Y      Year              4 0
C                   Extrct    Date1:*M      Month             2 0
C                   Extrct    Date1:*D      Day               2 0




Date Mathematics

The new versions of RPG offer a range of new tools for date mathematics.

ADDDUR

The ADDDUR verb allows you to add a specified duration to a date or time.


dDate_From        s               D   INZ(D'2004-01-31')       
dDate_To          s               D                            
dTime_From        s               T   INZ(T'01.00.00')         
dTime_To          s               T                            
c                                                              
c*    30 days from 2004-01-31 takes us to 2004-03-01           
c                   ADDDUR    30:*D         Date_from          
                                                               
c*    backup 30 days backwards takes us back to 2004-01-01     
c                   ADDDUR    -30:*Days     Date_from          
                                                               
c*    30 days from 2004-01-31 takes us to 2004-01-31           
c     Date_from     ADDDUR    30:*Days      Date_To            
                                                               
c*    30 minutes from 01.00.00 takes us to 01.30.00            
c     Time_from     ADDDUR    30:*Minutes   Time_To           
                                                              
c                   Eval      *inLR         = *on             


Notice the notation 30:*D .This is interpreted by the compiler as 30 days. You can also also write 30:*Days for somewhat greater clarity. ADDDUR and other date operations mentioned here can also be used can use increments *Days, *Months, *Years, *Hours, *Minutes and *MS (miliseconds). There is a 15 digit limitation on the duration (eg: 999,999,999,999,999 days), however this does not pose a much of a problem.

SUBDUR

Keyword SUBDUR allows you to find the duration of time between two dates


dDATE_from        s               D   INZ(D'2002-01-01')
dDATE_to          s               D   INZ(D'2002-01-02')
dDays             s              4  0

c     Date_to       SUBDUR    Date_from     days:*D


%DAYS %MONTHS %YEARS and %DIFF

The keywords ADDDUR and SUBDUR are not available in RPG /free. Instead IBM offers the powerful built-in functions (BIFs) called %DAYS, %MONTHS %YEARS and %DIFF.

d ToDate          s               D   INZ(D'2004-01-02')                
d FromDate        s               D   INZ(D'2003-01-02')                
d NextMonth       s               D                                     
d NextYear        s               D                                     
d Days            s              4  0                                   
 /free                                                                  
      // add 30 days                                                    
      Eval     NextMonth  = ToDate + %days(30)                   ;      
      // add 6 months                                                   
      Eval     NextYear   = ToDate + %months(12)                 ;      
      // calculate number of days between 2 dates                       
      Eval     Days       = %diff(ToDate : FromDate: *d )        ;      
      Eval     *inlr      = *on                                  ;      
 /end-free                                                              


EXTRCT: To extract Year/Month/Day information from a date field

dDATE1            s               D   INZ(D'2002-01-02')   
dYear             s              4  0                      
dMonth            s              2  0                      
dDay              s              2  0                      
C                   EXTRCT    Date1:*Y      Year           
C                   EXTRCT    Date1:*M      Month          
C                   EXTRCT    Date1:*D      Day            


Converting Dates to other Data Types

Convert Date to Numeric

In this example we get a numeric value of a date using the %subdt function.



 *--------------------------------------------------------------------- 
dDate_N           s              8  0                                   
dDate_D           s               D   INZ(d'2002-12-31')                
 *--------------------------------------------------------------------- 
 /free                                                                  
     // get a numeric value of a date using the %subdt function         
     // example: extract numeric 20021231 from date '2002-12-31'        
           Eval  Date_N   =  (%subdt(Date_D:*y)  * 10000) +             
                             (%subdt(Date_D:*m)  * 100)   +             
                             (%subdt(Date_D:*d))              ;         
           Eval *InLR = *On                                   ;         
 /end-free                
                                               

Convert Date to String

Because the date data type seems similar to a character data type, you may be tempted to treat it like a string. This is not always a good idea, as following substring example illustrates:


dDate1            s               d   Inz(d'2002-01-30')               
dDateString       s             10    Inz( '2002-01-30')               
dYearString       s             10                                     
dYearNumeric      s              4  0                                  
C* This will work because the substring function will operate on a string
C                   Eval      YearString  = %subst(DateString:1:4)     
C*
C* This will NOT work because the substring function will NOT operate on a date field
C                   Eval      YearNumeric = %subst(Date1:1:4)          

Converting Alphanumeric or Numeric data to Date Type



You can convert alphanumeric or numeric fields to type Date.  This is useful when bridging old code to your new files or when importing external data sources. Before such a conversion, it would be strongly advised that the numeric or character data be tested to ensure that the data represents a valid date before converting it to a type Date.

Testing a string or numeric to ensure that it is a valid date

The TEST(D) OpCode allows you to test the validity of date. This following test of a numeric value will test successful  (*IN50 will be = *off) because 12/31/02 is a valid date of type MM/DD/YY.

 
 dDateNumeric      s              6  0
 c                   eval      DateNumeric  = 123102
 c     *mdy          test(d)                 DateNumeric            50

Here an eight character string containing '12/31/02' will test successfully.


 dDateString       s              8
 c                   eval      DateString = '12/31/02'
 c     *mdy          test(d)                 DateString             51



Capturing Today's Date

Get the system date and assign its value to a type Date field. In one case the system date is assigned to a numeric field, then to a date field. In the second case,  we assign the today's date to the date field Date2,

 hDatedit(*ymd)
 d Date_N8         S              8  0
 d Date1           S               D
 d Date2           S               D
 c* Retrieve current date, move to numeric field then to date field
 c                   Eval      Date_N8   = *Date
 c                   Move      Date_N8       Date1

 c* Retrieve current date, move directly to date field
 c                   Move      *date         Date2


using the RPG /free feature we can assign a dates even more easily and explicitly.

 

d today           S               D                                
 /free                                                             
    // set today to today's date.                                  
    today =  %date(*date)                  ;                       
    *inlr   = *on                          ;                       
 /end-free                                                         

  


Assigning a string to a date field

It is also possible to use the %date function to assign a string containing a valid date to a date field.

 
d  mydate         s               d                    
d  mystring       s             10                     
 /free                                                 
           eval mystring = '2001-01-01'        ;       
           eval mydate =  %date(mystring)      ;       
           eval *inlr = *on                    ;       
 /end-free                                              
    


Here is an example where we manually assemble a timestamp field.



DTimeStampStr     DS                                                    
D  Stamp                        26    Inz('0000-00-00-00.00.00.000000') 
D  Century                       2    OverLay(Stamp:1)                  
D  Year                          2    OverLay(Stamp:3)                  
D  Month                         2    OverLay(Stamp:6)                  
D  Day                           2    OverLay(Stamp:9)                  
D  Hour                          2    OverLay(Stamp:12)                 
D  Minute                        2    OverLay(Stamp:15)                 
D  Second                        2    OverLay(Stamp:18)                 
D  MSecond                       6    OverLay(Stamp:21)                 
D  TimeStampTS    S               Z                                     
c                   Eval         Century   =  '20'                      
c                   Eval         Year      =  '04'                      
c                   Eval         Month     =  '02'                      
c                   Eval         Day       =  '13'                      
c                   Eval         Hour      =  '01'                      
c                   Eval         Minute    =  '59'                      
c                   Eval         Second    =  '30'                      
c                   Eval         MSecond   =  '000000'                  
c                   Move      TimeStampStr  TimeStampTS                 
c                   Seton                                        LR     

















































Hosted by www.Geocities.ws

1