3 Replies Latest reply: Aug 24, 2016 8:35 PM by Grant Perkins RSS

    Change Date Format Data Prep Studio

    Tracy Garon Hojka

      I am bringing in text file data (comma delimited file) with date fields formatted as YYYY/MM/DD or 2016/06/12 and I would like to reformat this to MM/DD/YYYY or 06/12/2016.  Currently Data Prep Studio is identifying this field as text.  Is there a way to first convert this field to numeric - without losing the actual data present in the field - and then reformat to the date format I mentioned above?  If so, please provide details on how I can accomplish this.

       

      Please note that I have attempted to first convert this field to numeric and all data within that field disappeared.

       

      Thanks!  Tracy Garon Hojka

        • Re: Change Date Format Data Prep Studio
          Grant Perkins

          Hi Tracy,

           

          Data Prep and Monarch will initially interpret and display dates that are recognised in the input files if they are in a format that matches the settings of your "Locale" for your system.

           

          If the incoming data cannot be fitted to an recognised format for the locale it will be brought in as text (usually) due to the separators that delineate the year from month from date.

           

          For each Workspace or Monarch Model you can change the format that will be recognised and displayed to allow you to work with reports that have a different "standard" applied. This will apply to the entire Workspace or Model.

           

          In Data Prep Studio click on the gear wheel at the top right of the window to get into Settings. Click on the "Conversion" tab and you will have access to the options that allow the recognised date format to be modified.

           

          For Monarch go to the Home Tab, then Options (2 gearwheels, top left) and then the INPUT tab.

           

          This may be all you need.Or maybe that an a little more format adjustment.

           

          However, if the incoming data has a mix of possible date formats - and that is not unusual from a text base source file - you may need to do more if you want to use the dates as dates. (If you only need to display them then the original text may be enough.)

           

          You can use a Calculated Field (also known as a Formula Field)  to take the input of one field and turn it into something else. So with a date that does not conform to the required standard you can chop up the input date, re-order it, change the presentation formatting (/ to - for example) and make it look how you need it to look.

           

          If you are changing it for internal use within the current report and its "standard" format you can set the result to be a Date field.

           

          Once you have the new field you may want to hide the original input field.

           

          However there are times when people need to change the format for output for a file that is to be passed on to another system that will be looking for a different standard date format, in which case you might send the field as text in some cases.

           

          In general fields that are successfully identified as Date fields will actually be storing a Date Serial Number and so passing from one system to another as  date field should be consistent even if the Locale format changes. But if the incoming field is only recognised as text that serial number will not be created at the point of input, only by later changes as discussed above.

           

          If you are familiar with Calculated fields you will likely be able to work out what to do if you need that approach.

           

          If not familiar ... I would suggest firstly have a look as the Help information about dates and the Functions related to Conversions and Dates.

           

          Usually the "CtoD" function is the obvious option (Character to Date) in the situation you described but there are other options and approaches should they be required. However there may be something else that would suit you better.

           

          You may well find a tutorial somewhere that would be usefully enlightening about the options. And of course I hope you have access to the Learning Guide. Understanding the possibilities for this subject is great for working out how to approach similar needs in the future.

           

          If you get stuck just come back and tell us what you did and what didn't work and we should be able to get you going again.

           

          HTH.

           

           

          Grant

            • Re: Change Date Format Data Prep Studio
              Tracy Garon Hojka

              Grant~

               

              The first option (gear wheel) worked perfectly!  I have the date formatted properly now!  Thank you so much for your help and the wonderfully complete information!

               

              Have a great day!  Tracy

                • Re: Change Date Format Data Prep Studio
                  Grant Perkins

                  Hi Tracy,

                   

                  I'm glad that worked for you.

                   

                  My apologies for the long explanation but as you will have realized people tend to search the entries (many of which date from some years back) to seek instant solutions for their problems.

                   

                  Date processing challenges can manifest themselves in several ways - reports with multiple date formats deployed are not entirely unknown! That can be fun.

                   

                  So I thought I had better try to cover the more common and perhaps easiest to solve problems in case you needed them and for the benefit of people dropping in at some future point.

                   

                  If you are fortunate, most of the inputs you have to deal with will be neat and consistent. But if for some reason they are not, don't give up on them. There are a lot of ways to approach problem inputs that may not be obvious at first sight.

                   

                   

                   

                   

                  =====================================================================================================

                   

                   

                  For anyone who has read this thread and not yet found an answer to their problem - things can get more complicated but then they are best dealt with through specific help in context rather than the more generic ideas set out earlier in the thread..

                   

                  Grant