1 Reply Latest reply: Feb 10, 2016 11:55 AM by Olly Bond RSS

    Simple question - Concatenating Date Functions

    Austin Scott

      I found this thread that asks the same question, but it is old and I can't find an answer via it.

       

      Concatenate date

       

      What I'm trying to do is take a date field (format "mm/dd/yyyy") and change it via a calculated field to "mm/15/yyyy". I thought something as simple as

       

      month(report_field) + "/15/" + year(report_field)

       

      Why does it hate that?

       

      The field format I've selected for the calculated field is "Date/Time" - ShortDate (and tried general).

       

      Error: The expression for this calculated field can't be evaluated.

       

      Any help? I'm on Monarch Pro 11.5

        • Re: Simple question - Concatenating Date Functions
          Olly Bond

          Hello Austin,

           

          So for any date in each month, you want to hack it to the 15th? I think there are built in functions for the Last and First dates of each month, but this is easy enough using the trick of converting dates to text and back again.

           

          You're on the right track, but month() is a number, not text, as is year(), so you need to first of all make these into text using str(), then put the whole string back into date format using ctod().

           

          ctod(str(month([Ship Date]);2;0;"0")+"/15/"+str(year([Ship Date]);2;0;"0"))

           

          works on the ShipDate field in Lesson11 and Classic.prn from the Tutorial files.

           

          Best wishes,

           

          Olly