5 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Convert Date to Date format

    gico1972 _

      I currently have a Date field which is populated with date in following format however i want to change this to DD-MMM

       

      [U]Date[/U][/B]

      10JAN

      20MAR

      20SEP

      16NOV

       

      Any Help would be very much appreciated.

       

      Thanks 

        • Convert Date to Date format
          joey

          I think you are actually talking about Character fields instead of Date Fields (even though the string inside the field represents a date).  Please correct me if I'm mistaken there.

           

          If this is a string, then you will need to add one calculated field to insert the dash.

           

          left(DateField,2) + "-" + Right(DateField,len(DateField) - 2)

           

          I'm assuming here that you have leading zeros for days 1 - 9 in the month.  If not, the expresion would be a little more complex.

          • Convert Date to Date format
            Grant Perkins

            If the format is always 5 characters  - i.e. for the 1st of a month you see 01MMM - then you can simply create a calculated field something like

             

            LEFT(date,2)"-"RIGHT(date,3)

             

            If it is not always 5 characters BUT the month is always 3 characters then something like

             

            LEFT(date,(LEN(Date)-3))"-"RIGHT(date,3)

             

            should do it.

             

            You may need some TRIM() function activity if you come across leading or trailing spaces in the field that affect the way the formula presents its results.

             

            HTH.

             

             

             

            Grant

             

            Edit: Ah! Parallel posting. Joey is quite right to point out that you are dealing with a character field rather than a date.

              • Convert Date to Date format
                gico1972 _

                Perhaps i am confusing the issue on this.....

                 

                The date in my template is currently a CHaracter field and i want to convert the Current format to a date field so that i can sort dates. By using the above calculated field to create a new Date field means that Data gets sorted by the numerical value and not as dd-mmm date.

                 

                Sort data shows as

                 

                01APR

                01FEB

                01JAN

                01MAR

                02APR

                02FEB

                02JAN

                02MAR

                 

                Thanks for all your help on this.

                  • Convert Date to Date format
                    Grant Perkins

                    You will need a YEAR from somewhere for a date field.

                     

                    If you have a source you can concatenate the year onto the end of you DD-MMM and then convert it to a date using the CtoD() function.

                     

                    Check out the Help file for supported date formats.

                     

                    If you want to sort on what you have then separate the component parts into two fields, give the Month a number as an alternative and sortable (but possibly hidden field) value and use both fields for sorting your table/summary.

                     

                    It's not confusing but we do need to know what you are setting out to do to be able to offer useful advice.

                     

                    HTH.

                     

                     

                    Grant

                      • Convert Date to Date format
                        Data Kruncher

                        If you can safely assume that the dates in question occur in the same year as the year in which you're using the model (right now), you could use this expression to generate your proper date field:

                         

                        [SIZE=2]CtoD(Left(Mydate,2)"/"Right(Mydate,3)"/"Str(Year(Date())),"d/m/y")[/SIZE][/CODE]

                         

                        Otherwise you could build some logic that would do something like if the current month is January then the year for the dates is (this year - 1).

                         

                        A better way would be to extract the reporting year from the data source itself, if possible.

                         

                        HTH,

                        Kruncher