3 Replies Latest reply: May 15, 2014 10:02 AM by Dan Kinney RSS

    Date Conversion

      I have a DATE/TIME field that I want to convert to a date in the format of MM/DD/YYYY.

       

      The current field is in this format - Tuesday, August 19, 2008 2:36 AM

       

      I want to convert the above to 08/19/2008; I don't care about the time.

       

      I have done it the long way creating calculated fields, but I'm sure there has to be an easier way. I have tried the Ctod function, but have not had any luck.

       

      I appreciate any help. I am using Monarch Pro V9.01.

        • Date Conversion
          Grant Perkins

          Hi Dan,

           

          Hmm, dates. Always a stimulating challenge.

           

          If you skip the day name and use the rest of the date Monarch, with the appropriate Input Options set, can give you 8/19/2008 directly on read.

           

          If you need the 08 for the month you probably need a calc field and character type rather than date tyep unless I am forgetting something.

           

          I'll guess that you probably need something like capture "long string" and then post processing to get the date string without the day name. You may be able to use a floating trap. Maybe.

           

          As you have V9 you coudl save the result as a User Defined Function for future re-use.

           

          How far do these suggestions get you?

           

           

           

          Grant

            • Date Conversion
              Data Kruncher

              Gents,

               

              Rather than try and potentially struggle with a floating trap (depends on your report a bit I suspect), I'd be tempted to trap one long string and "post process" as Grant recommends.

               

              With the long string named RptDate, you'll get a leading zero MM/DD/YYYY date with this formula in a Character type calculated field:

              right("0"trim(str(month(ctod(lsplit(rptdate,2," ",2),"m/d/y")))),2)"/"+

              right("0"trim(str(day(ctod(lsplit(rptdate,2," ",2),"m/d/y")))),2)"/"+

              trim(str(year(ctod(lsplit(rptdate,2," ",2),"m/d/y"))))[/code]It's a bit messy looking when you do it all in one calculated field like this, but it works.

               

              Alternatively, you can make it a bit more readable by taking the basic date calculation out to its own Date field with:

              ctod(lsplit(rptdate,2," ",2),"m/d/y")[/code]Then your LeadingZeroDate becomes:

              right("0"trim(str(month(MyDate))),2)"/"+

              right("0"trim(str(day(MyDate))),2)"/"+

              trim(str(year(MyDate)))[/code]

               

              Kruncher

                • Date Conversion

                  It never fails, but I pretty much nailed it shortly after I posted.

                   

                  The input source is an E-Mail so the date always consistently starts in the same column. So I then created a trap that was long enough to capture the longest date string possible.

                   

                  Then I created a calculated field using the Extract function to trim off the left part of the data. Which gave me:

                   

                  August 19, 2008 2

                   

                  Extract(," ",":")

                   

                  Then I created another calculated field using the Rsplit function to trim off the right part of the data. Which gave me just the date in Month Day, Year format:

                   

                  August 19, 2008

                   

                  RSplit(,2," ",2)

                   

                  Then I created a calculated field using the Ctod function which worked like a champ. This was about 5 or 6 less steps then how I originally accomplished it.

                   

                  Ctod(,"m/d/y")

                   

                  Then I created a calculated field using the Dtoc function to convert it back to a Character filed so that when I exported it would not truncate the leading zeros.

                   

                  I do like the suggestion of creating a user defined function so that I can re-use this going forward without have to recreate it each time. I suspect that I will be creating lots of models from e-mail reports that will always have the same date format. That will be another new Monarch feature that I have not used before which I know will help me down the road.

                   

                  Thanks again for your responses and suggestions.