6 Replies Latest reply: May 15, 2014 9:51 AM by Grant Perkins RSS

    Date format question for Monarch Pro 7.x

    Hank @ Adtran

      I have a report that has a date field in the format of mm/yy (06/06 would be this month) and I have tried the different date/time formats and cannot seem to get it to work. The only format that works is the char format and I have to go back in the DB and change to a date/time datatype. I have picked this up from a previous employee and am a total newb in Monarch. Any help is greatly appreciated.

       

      Best regards,

      Hank

        • Date format question for Monarch Pro 7.x
          Grant Perkins

          Hank, welcome.

           

          Am I right to assume you need to export the 'date' in the same format (mm/yy ) to a database but that that database needs to understand it as a date and time field?

           

          To be honest I can't see how a system can work out a suitable date/time field entry from just month and year unless some assumptions are being made which modify the value before sending it out.

           

          However I could well be wrong and no doubt one or more of the other forum participants will have some suggestions.

           

          Would be good just to be sure I have correctly understood what the needs are.

           

          Grant

          • Date format question for Monarch Pro 7.x
            Hank @ Adtran

            Originally posted by Grant Perkins:

            Hank, welcome.

             

            Am I right to assume you need to export the 'date' in the same format (mm/yy ) to a database but that that database needs to understand it as a date and time field?

             

            To be honest I can't see how a system can work out a suitable date/time field entry from just month and year unless some assumptions are being made which modify the value before sending it out.

             

            However I could well be wrong and no doubt one or more of the other forum participants will have some suggestions.

             

            Would be good just to be sure I have correctly understood what the needs are.

             

            Grant /b[/quote]Hi Grant,

            If I can reformat into a usable mm/dd/yyyy format that would be great. Sorry for being vague. I need to take the current format mm/yy and reformat it using the model to output mm/dd/yyyy (basically taking 06/06 and making it 06/01/2006). Does that help?

             

            Thanks,

            Hank

            • Date format question for Monarch Pro 7.x
              Grant Perkins

              Hank,

               

              Try creating a calculated field which uses the following formula and then export that.

               

              Ctod(LSPLIT(,2,"/",1)"/01/"LSPLIT(,2,"/",2))

               

              This assumes that the input MM/YY field is called month-year.

               

              The formula Lsplits the character field at the "/" and firstly uses the first part of the split (in this case the first two characters), concatenates "/01/" on the middle to be used as the day of the month and then adds the second part of the split for the year.

               

              The resulting character field would be mm/01/yy.

               

              CtoD is the Character to Date function in Monarch which will convert the text format to a date field - remember to make the field type a date field. You can specify the format (see the help for details) but you will probably find that your default will work OK. That said, because dates are set as default system parameters and can be overridden for specific purposes in many applications, including Monarch, be sure to check that you get or set and get what you need!

               

              The output format in your new date field will provbably display the 20 of 2006 automatically - again this is a system related parameter and cosmetic rather than related to the underlying data in the field.

               

              Check the exported results as well, just to make sure that the date field in both systems are consistent in their understanding. I doubt it will be a problem but always worth checking.

               

              There may well be a few other ways to do this but this one is quick and easy and should suffice.

               

              HTH.

               

              Grant

               

              [size="1"][ June 23, 2006, 11:00 AM: Message edited by: Grant Perkins ][/size]

              • Date format question for Monarch Pro 7.x
                Hank @ Adtran

                Thanks Grant I will look a creating that and let you know how it turns out.

                 

                Hank

                • Date format question for Monarch Pro 7.x
                  Hank @ Adtran

                  **update**

                  <note to self> Search forum before asking questions...found the answer in a previous post. "Edit" --> "Hide/Display Fields"...

                   

                  <old stuff>

                  Seems to be working great. One follow-up question: Can I hide the source fields and only output the calculated fields?

                  • Date format question for Monarch Pro 7.x
                    Grant Perkins

                    Originally posted by Hank @ Adtran:

                    **update**

                    <note to self> Search forum before asking questions...found the answer in a previous post. "Edit" --> "Hide/Display Fields"...

                     

                    <old stuff>

                    Seems to be working great. One follow-up question: Can I hide the source fields and only output the calculated fields? /b[/quote]:D 

                     

                    I find the "Field List" feature is a great window on the internal Monarch world for hiding and unhiding fields and many other things. Highly recommended!

                     

                    Glad you have a solution.

                     

                    All the best.

                     

                     

                    Grant