7 Replies Latest reply: May 15, 2014 9:56 AM by Nick Osdale-Popa RSS

    Date Format

    MonarchUser _

      I have a field "Date" in the format 1/1/2006.  My goal is to just change the format to "dd-mmm-yyyy".I am not sure how to do this. Any help would be appreciated.

       

      I have monarch version 7.

       

      [size="1"][ October 05, 2006, 11:10 AM: Message edited by: MonarchUser ][/size]

        • Date Format
          Data Kruncher

          You'd think this would be simple. Unfortunately, the Ctod function, which you can generally use for this type of stuff, does not support that specific date format.

           

          So my solution to this is to create a calculated character field with the following formula which references the poorly named original field "mydate":

          [font="courier"]str(day(mydate),2,0,"0")"-"

          if(month(mydate)=1,"Jan",

          if(month(mydate)=2,"Feb",

          if(month(mydate)=3,"Mar",

          if(month(mydate)=4,"Apr",

          if(month(mydate)=5,"May",

          if(month(mydate)=6,"Jun",

          if(month(mydate)=7,"Jul",

          if(month(mydate)=8,"Aug",

          if(month(mydate)=9,"Sep",

          if(month(mydate)=10,"Oct",

          if(month(mydate)=11,"Nov","Dec")))))))))))

          "-"ltrim(str(year(mydate)))[/font][/quote]This will give you a dd-mmm-yyyy formatted date.

           

          HTH,

          Kruncher

          • Date Format
            MonarchUser _

            Thanks a lot. It worked.

            • Date Format
              Grant Perkins

              A quick note to observe that Look-Up fans could also consider creating an internal look-up table to map the month number to the text for the month.

               

              If it is a lookup that is uesed a lot, and a Pro version of Monarch is available, and external lookup file might be a way to go. If there are many users on a network that one file could be used by all.

               

              Kruncher's suggestion is already proven of course and my suggestion is offered simply as a catalyst to suggest how flexible Monarch can be.

               

              HTH.

               

               

              Grant

              • Date Format
                Nick Osdale-Popa

                Originally posted by Data Kruncher:

                [quote][font="courier"]str(day(mydate),2,0,"0")"-"

                if(month(mydate)=1,"Jan",

                if(month(mydate)=2,"Feb",

                if(month(mydate)=3,"Mar",

                if(month(mydate)=4,"Apr",

                if(month(mydate)=5,"May",

                if(month(mydate)=6,"Jun",

                if(month(mydate)=7,"Jul",

                if(month(mydate)=8,"Aug",

                if(month(mydate)=9,"Sep",

                if(month(mydate)=10,"Oct",

                if(month(mydate)=11,"Nov","Dec")))))))))))

                "-"ltrim(str(year(mydate)))[/font][/quote][/b][/quote]I examined this formula and come up with a Proof Of Concept[/i] that there's more than one way to accomplish the same task in Monarch:

                 

                [font="courier"]Str(Day(),2,0,"0")"-"

                Substr("JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",(Month()*3)-2,3)

                "-"LTrim(Str(Year()))

                 

                --

                -- or if you wanted to seperate the months for readablity --

                --

                 

                Str(Day(),2,0,"0")"-"

                Substr("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",(Month()*4)-3,3)

                "-"LTrim(Str(Year()))[/font][/quote]Just my 2c worth!   

                 

                 

                EDIT: you could also pad the Substr() with spaces, instead of using -2 (first formula) or -3 (second formula) thus the function would look like these, respectively:

                [font="courier"]Substr("  JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC",(Month()*3),3)

                .

                Substr("   JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC",(Month()*4),3[/font][/quote]And last but not least you coul also do the full months name as long as each one was 9 characters long (September is the longest string) and just use the Trim() to take out the extra spaces. Remember, in this case the beginning number of spaces would be 8.

                  :cool:

                 

                [size="1"][ October 05, 2006, 01:52 PM: Message edited by: Nick Osdale-Popa ][/size]

                • Date Format
                  Data Kruncher

                  Grant, now that you mention it, I seem to recall a similar discussion relating to internal lookups being posted in the last few months. I think I need to stop posting responses before, say, 10:00 am.    

                   

                  Nick, once again, very slick. You have a secret desire to stop coding in VB and start optimizing standard C code, don't you?  :cool:  Next you'll have tips that look like  [font="courier"]std_ptr == i++ /font[/quote]or something.        

                   

                  Have fun today,

                  Kruncher

                  • Date Format
                    Grant Perkins

                               

                     

                    Could this be time for a competition to uncover who in the forum has the most "something" way to satisfy this sort of requirement?

                     

                    We could have various categories:

                     

                    Most subtle.

                     

                    Most elegant

                     

                    Most succinct

                     

                    Most over-the-top

                     

                    Most obscure

                     

                    Most complex

                     

                    Shortest, Longest

                     

                    ... and so on.

                     

                    Maybe Mike could come up with a prize for the 'Most Interesting' offerings?

                     

                    (Sorry for the thread hijack - if anyone takes this idea forward could they start a new thread please - preferably with a sticky?)

                     

                     

                    Grant

                    • Date Format
                      Nick Osdale-Popa

                      Originally posted by Data Kruncher:

                      Grant, now that you mention it, I seem to recall a similar discussion relating to internal lookups being posted in the last few months. I think I need to stop posting responses before, say, 10:00 am.      

                       

                      Nick, once again, very slick. You have a secret desire to stop coding in VB and start optimizing standard C code, don't you?   :cool:   Next you'll have tips that look like  [font="courier"]std_ptr == i++ /font[/quote]or something.            

                       

                      Have fun today,

                      Kruncher /b[/quote]Every program has at least one bug and can be shortened by at least one instruction -- from which, by induction, it is evident that every program can be reduced to one instruction that does not work.