3 Replies Latest reply: Apr 21, 2015 1:03 PM by Grant Perkins RSS

    convert date as character to a true date field

    Gatter _


      I have a column with a date as a character field "150430" where this should be 04/30/2015.  I've banged my head against the keyboards enough times already.  How can I convert this?

        • Re: convert date as character to a true date field
          Grant Perkins

          Hello Gatter,

           

          You don't say which version of Monarch you have but assuming it's not something really ancient what you need is a calculated field in your table and the CtoD function for the formula. Have a look at the Help for the function where you should find a full description and a worked example.

           

          Dates are a little challenging sometimes when providing suggestions because your local system default date formats, whatever is defined for you Monarch preferences and what is in the report can all introduce some interesting conflicts.

           

          Since you source field is already a Character field a formula such as :

           

          ctod(source_field_name,"y/m/d")

           

          That should return a date in your local system date format. If if doesn't you can add an output parameter as described in the help.

           

          [NOTE: If your field had started out as Numeric instead of character you could convert it to character and then apply CtoD in a single formula by using the STR() function.            ctod(str(Field,6),"y/m/d")  ]

           

           

          There are a number of other ways you could approach the challenge, including, but not limited to, using Monarch's text manipulation functions to split the source field into the date format you want piece by piece. Sometimes that can be useful with date requirements but in this case the CtoD function looks like the most obvious solution.

           

          Hope that helps and saves your head from further damage!

           

           

          Grant