Need to take date/time from xml like: '10/12/2016 14:30' and place into a timestamp field

Yes.  SQL? RPG?

ile-rpg free form if possible

Timestamp literals are specified in RPG in ISO format: yyyy-mm-dd-hh.mm.ss.uuuuuu.  So you could parse the string and get it into ISO format and use %timestamp BIF to assign to TIMESTAMP type variable.

In this case, probably easier to parse date and time individually and just add them together to get timestamp.  I didn't test this, but should be close enough for you to get the idea.  Find the location of the space between time and date, and extract each separately.

timeString = '10/12/2016 14:30' ;
delimLoc = %scan ( timeString : ' ' ) ;
myTimestamp = %date( %left ( timeString : delimLoc-1 ) : *USA ) +
                              %time ( %subst ( delimLoc+1 : %len ( timeString ) - delim ) + ':00' : *HMS) ;

Crap, the original test xml data was crap.

Can you help me get this into a timestamp?

2016-10-27-15:52:05

Thanks and sorry!

When you have a known consistent format such as 'YYYY-MM-DD-HH:MI:SS' (which matches 2016-10-27-15:52:05), Gary's answer is best. You know exactly how long the year is, where the dashes and colons are and it's always the same. Nice and straightforward as his sample code shows.

When it might be 'YYYY-MM-DD-HH:MI:SS' sometimes, 'YYYY-MM-DD HH:MI:SS' other times (missing a '-'), and maybe even 'YY-MM-DD-HH:MI:SS', then you need flexibility.

I butchered together a module that can demonstrate a couple of the ILE CEE* APIs to handle some changes in formats. This sample uses pieces I have from other modules, but it shows the possibility of getting a timestamp out of a chosen format when that choice might need to change:

     H Debug( *yes )
     H*Debug( *no )
     H Nomain
     H*DFTACTGRP(*NO)

     d CHAR255_        s            255
     d TIMESTAMP_      s             26
     d VSTRING_        ds                  inz
     d  l2pstring                    +2    like( CHAR255_ ) overlay( VSTRING_ )
     d    l2len                       5i 0 overlay( l2pstring : 1 )
     d    l2string                  255    overlay( l2pstring : 3 )
     d  l4pstring                    +4    like( CHAR255_ ) overlay( VSTRING_ )
     d    l4len                      10i 0 overlay( l4pstring : 1 )
     d    l4string                  255    overlay( l4pstring : 5 )
     d  stringz                       1    overlay( VSTRING_ )

     d FEEDBACK_       ds                  inz
     d  MsgSev                        5i 0
     d  MsgNbr                        5i 0
     d  Flags                         1a
     d    Case                             like( Flags ) overlay( Flags )
     d    Severity                         like( Flags ) overlay( Flags )
     d    Control                          like( Flags ) overlay( Flags )
     d  Facility_ID                   1    dim( 3 )
     d  I_S_Info                     10u 0

     d CeeCvtTimestringToSecs...
     d                 pr             8f   extproc( 'CeeCvtTimestringToSecs' )
     d peTimestring                  26    const
     d pePicture                     26    const

     d CeeCvtSecsToTimestamp...
     d                 pr                  extproc( 'CeeCvtSecsToTimestamp' )
     d                                     like( TIMESTAMP_ )
     d   peSecs                       8f   value

     P*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     p CeeCvtTimestringToSecs...
     p                 b                   export
     d CeeCvtTimestringToSecs...
     d                 pi             8f
     d peTimestring                  26    const
     d pePicture                     26    const

     d CEESECS         pr                  extproc( 'CEESECS' )
     d                                     opdesc
     d   TimeStamp                         like( VSTRING_ ) OPTIONS( *VARSIZE )
     d                                     const
     d   Picture                           like( VSTRING_ ) OPTIONS( *VARSIZE )
     d                                     const

     d   seconds                      8f
     d   feedbackCode                      like( FEEDBACK_ ) options( *OMIT )

     d theNbrOfSecs    s              8f
     d fc              s                   like( FEEDBACK_ )

      /free
         callP     CEESECS ( peTimestring :
                             pePicture :
                             theNbrOfSecs :
                             fc );
         return ( theNbrOfSecs );
      /end-free

     p                 e


     P*+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
     p CeeCvtSecsToTimestamp...
     p                 b                   export
     d CeeCvtSecsToTimestamp...
     d                 pi                  like( TIMESTAMP_ )
     d   peSecs                       8f   value

     d CEEDATM         pr                  extproc( 'CEEDATM' )
     d                                     opdesc
     d   Seconds                      8f
     d   Picture                           like( VSTRING_ ) OPTIONS( *VARSIZE )
     d                                     const
     d   Timestamp                         like( TIMESTAMP_ )
     d   feedbackCode                      like( FEEDBACK_ ) options( *OMIT )

     d wwTimestamp     s                   like( TIMESTAMP_ )
     d fc              s                   like( FEEDBACK_ )

      /free
         callP  CEEDATM ( peSecs :
                          'YYYY-MM-DD-HH.MI.SS' :
                          wwTimestamp :
                          fc );
         return ( wwTimestamp );
      /end-free

     p                 e

Select all Open in new window

The module has two exported procs, CeeCvtTimestringToSecs() and CeeCvtSecsToTimestamp(). The CeeCvtTimestringToSecs() proc calls the CEESECS() ILE CEE* API to convert some date/time string into a value representing the number of seconds since 00:00:00 14 October 1582. (You can look up why that date is meaningful if you wish.) The CeeCvtSecsToTimestamp() proc calls CEEDATM() to convert the number of seconds into an ISO timestamp.

Here's a simple CL module that you can bind with the example to test:

pgm


   dcl   &timeString  *char   26     value( '2016-10-27-15:52:05' )
   dcl   &picture     *char   26     value( 'YYYY-MM-DD-HH:MI:SS' )
   dcl   &timeStamp   *char   26     value( ' ' )
   dcl   &secs        *char    8     value( x'0000000000000000' )


   callprc    'CeeCvtTimestringToSecs' ( &timeString   +
                                         &picture    ) +
                                 rtnVal( &secs )
   callprc    'CeeCvtSecsToTimestamp' ( &secs        ) +
                                rtnVal( &timeStamp )


 dmpclpgm

   return

endpgm

Select all Open in new window

Compile the two modules. Then use CRTPGM to bind them together. The RPGLE module could even be part of a larger date-handling service program.

When the program runs, you should find dump output that shows the final timestamp value is '2016-10-27-15.52.05      '. (The length here is longer to allow for possible fractions of a second if you ever need to add that.)

The CL passes your given date into the first proc along with a "picture" string that describes the date/time string. The return value is the number of seconds for that date/time. Then the number of seconds is passed into the second proc. The returned value is an ISO timestamp.

You can try changing the date/time value in the CL while also changing the picture to match. As long as you give a valid date and the appropriate picture, the final result should always be the date/time in ISO timestamp format.

Although this is written so that the two APIs are in separate procs, you might simply code them one after the other in a single proc, or simply in-line in whatever code you'll use to process date/time values. By separating them, it's easier to make numerous future changes as you learn what the ILE CEE* APIs can do.

@Tom:  what a great general-purpose solution.  Thanks for sharing.  It is going in my toolbox.

Thanks tom, like Gary, nice tool for my toolbox

@Gary, keep aware that it was "bent" to fit this specific request (and it ignores errors returned by the APIs). You understand all that, I'm sure; but others will want to test/explore carefully due to potential confusion. The ILE CEE* APIs are well worth studying.

CEE date APIs: where else do you get to learn the meaning of "Lillian date" and "proleptic Gregorian calendar".  Fun times.