RE: Dates in POI Sql2Xls

["Barton, Mike" <Mike.Barton@xxxxxxxxxxxxxxxxx>]

Bob
        Sounds like you're getting hung-up re re-formating dates...

Bits I forgot earlier...

* // Date.                                                 
D date            S               O   CLASS(*JAVA           
D                                     :'java.util.Date')
* // Date Cell Style                                                 
D dateCellStyle   S               O   CLASS(*JAVA                     
D                                     :'org.apache.poi.hssf.usermodel-
D                                     .HSSFCellStyle')
* // cellStyle.Set Data Format                                         
D cellStyleDfmt   PR                  EXTPROC(*JAVA                     
D                                     :'org.apache.poi.hssf.usermodel-  
D                                     .HSSFCellStyle'                   
D                                     :'setDataFormat')                 
D Parm1                          5I 0 value                             

D valueDATE       S             20i 0
// Create date/time cell styles    
dateCellStyle = crtCellStyle(workbook);  
cellStyleDfmt(dateCellStyle : 15);


So, if your date field (FileDateField) on file is a proper date then do
this:

valueDATE = %diff(FileDateField : %date('1970-01-01':*iso) : *days); 
valueDATE = valueDATE * 24 * 60 * 60 * 1000;                      

or if it is an 8/0 numeric in CCYYMMDD format use:
D dateField       S               d       DATFMT(*ISO)
C                   MOVE       fileDateField    dateField
valueDATE = %diff(dateField : %date('1970-01-01':*iso) : *days); 
valueDATE = valueDATE * 24 * 60 * 60 * 1000;                      

then..

cell = createCell(row:c);    
setCellType(cell:0);         
date = createdate(valueDATE);
setCellValDate(cell:date);
setCellStyle(cell:dateCellStyle);

HTH

Mike
                -----Original Message-----
                From:   Bob O. [mailto:otis_the_cat@xxxxxxxxxxx]
                Sent:   08 March 2005 15:29
                To:     rpg400-l@xxxxxxxxxxxx
                Subject:        RE: Dates in POI Sql2Xls

                I tried that but I know I wasn't doing it right.  I had a
date field (type 
                "L") defined in my physical file.  When I tried doing a move
into the field, 
                I kept coming up with an "invalid date or time stamp"
message.  I guess I 
                just don't know the correct way to move the value into the
date field.  I 
                tried sever different ways but to no avail...

                My RPG program pulls in the hire date from an employee file.
This field is 
                defined as 8/0 numeric.  My problem arises when I multiply
this number by 
                10000.0001 to reverse it from yyyy/mm/dd to mm/dd/yyyy and
move it into the 
                date field

                >From Physical file DDS:
                A            CPHIRED         L          DATFMT(*MDY)
COLHDG('HIRE DATE')

                I was doing something like this (but have since removed the
code from the 
                program):

                C        PMHIRED   MULT       10000.0001             PMHIRED
<-- NUMERIC 
                (8/0)
                C        *MDY       MOVE       PMHIRED
CPHIRED    <-- 
                DEFINED WITH DATA TYPE "L"
        

                               IN THE PHYSICAL FILE ON ISERIES

                Is this what I should be doing?


                >


                ###########################################
                
                This message has been scanned by F-Secure Anti-Virus for
Microsoft Exchange.
                For more information, connect to http://www.F-Secure.com/

**********************************************************************
This communication and the information it contains: - (a) Is intended for the 
person(s) or organisation(s) named above and for no other person(s) or 
organisation(s). Access to this mail by anyone else is unauthorised. (b) Is 
confidential, and may be legally privileged or otherwise protected in law. 
Unauthorised use, circulation, copying or disclosure of any part of this 
communication may be unlawful. (c) May be susceptible to interference, and 
should not be assumed that it has come in its original form and/or from the 
stated sender or PinkRoccade UK accepts no responsibility for information, 
errors or omissions in this e-mail or use or misuse thereof or any act done or 
omitted to be done in connection with this communication. If you are not the 
intended recipient, please inform postmaster@xxxxxxxxxxxxxxxxx immediately and 
delete it and all copies from your system.

www.pinkroccade.co.uk
**********************************************************************