Solved

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

Posted on 2016-10-31
10
35 Views
Last Modified: 2016-11-04
Can the 10/12/2016 (char) be placed into a timestamp easily? The time 14:30 (char, no seconds) as well?
0
Comment
Question by:pipster1
  • 5
  • 3
  • 2
10 Comments
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 41867389
Yes.  SQL? RPG?
0
 

Author Comment

by:pipster1
ID: 41867453
ile-rpg free form if possible
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 41868004
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) ;
0
 

Author Comment

by:pipster1
ID: 41869264
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!
0
 
LVL 34

Accepted Solution

by:
Gary Patterson earned 500 total points
ID: 41869629
That's almost ISO format.  In V7R1 and later, you can use %SCANRPL BIF to replace ":" with ".":

timeString = '2016-10-27-15:52:05' ;
myTimestamp = %timestamp ( %scanrpl ( ':' : '.' : timeString ) ;

Or for something that also works In earlier versions, you can use %xlate:

timeString = '2016-10-27-15:52:05' ;
myTimestamp = %timestamp ( %xlate ( ':' : '.' : timeString ) ;
1
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 27

Expert Comment

by:tliotta
ID: 41873384
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

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

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.
1
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 41874023
@Tom:  what a great general-purpose solution.  Thanks for sharing.  It is going in my toolbox.
0
 

Author Closing Comment

by:pipster1
ID: 41874134
Thanks tom, like Gary, nice tool for my toolbox
0
 
LVL 27

Expert Comment

by:tliotta
ID: 41874144
@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.
0
 
LVL 34

Expert Comment

by:Gary Patterson
ID: 41874164
CEE date APIs: where else do you get to learn the meaning of "Lillian date" and "proleptic Gregorian calendar".  Fun times.
1

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

759 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now