2 Replies Latest reply: May 15, 2014 10:01 AM by ltw _ RSS

    Trapping and extracting long dates

    ltw _

      Those of you who have been doing this for a long time must know a good way to trap and extract long dates.  I provide the following as a simplified example:

      January 14, 2008   

         135812  $   115.00

         138199  $     6.25

         138297  $    30.00

         137557  $   155.56

      March 6, 2008

         137216  $    12.45

         138099  $    20.00

         138583  $     5.50

      April 3, 2008

         138287  $    60.00

         135810  $    65.00

         138219  $     9.54

      May 2, 2008     

         137733  $     6.25

         137937  $    12.50

         138323  $     6.25

      June 3, 2008   

         138323  $   394.30[/CODE]

       

      In this, the detail lines have an identifying code and an amount.  The date is then in the append line.  Because it is a long date, the length of the month part and the length of the day part vary, which causes the whole thing to vary in length. 

       

      To trap this, I made a floating trap on the comma and year portion.  I then tried selecting the month as a field.  The first thing that I notice is that the length of this is limited to the portion prior to the trap - it won't let me select the entire date as the field.  I recognize that this could be trapped in other ways, such as just looking for alpha characters in the first three positions, but I am really interested in how some of you might have dealt with this kind of thing in the past.

       

      I appreciate your thoughts on this.

        • Trapping and extracting long dates
          Data Kruncher

          Hi ltw,

           

          You're right, of course, the easiest way is to just create a nice wide field for the date, which you can easily define as a date type, and you're done.

           

          But the more sophisticated way would be to put a floating trap to work for you. And you're correct once again, in that you cannot paint the entire date as one field.

           

          Not a problem. We'll just put a calculated field together.

           

          In your template define the first field as MonthDay (character) up to, but not including, the comma. Now the Year field is a character field as well, from the comma to the last digit.

           

          Now in your table create a date type calculated field with the expression:

          [SIZE=2]

          ctod(MonthDay+Year,"m/d/y")

          /SIZE[/CODE]

           

          This will give you 1/14/2008 instead of January 14, 2008.

           

          If you really want the long text instead of a true date, just make a character field with:

          MonthDay+Year

          /CODE

           

          While they both can get you the same end result, the big benefit to the floating trap approach versus the alternate is that'll never have to worry about needing to "verify" your report for longer strings than you defined in the append template.

           

          Kruncher

          • Trapping and extracting long dates
            ltw _

            Kruncher,

            Thanks for the input.  I actually came up with the same solution - capturing in two pieces and concatinating them.  I just wanted to see if you experts had something better for this.

             

            Thanks!