3 Replies Latest reply: May 15, 2014 9:53 AM by ron cousino RSS

    Converting  a numeric to a string

    ron cousino

      Dates are stored as numerics in our database, i.e CCYYMMDD.  Since it is not a date field I need to be able to split the Year, month, and day.  Any suggestions on how to convert.  Currently using Monarch Pro 7.2.  Any CSTR functions in 8.0 like Crystal provides?

        • Converting  a numeric to a string
          LindaP _

          To split, how about calculated fields.

           

          Let's say your numeric date field from the database is .

          Year:

          Int(/10000)

           

          Month:

          Int(Mod(,10000)/100)

           

          Day:

          Mod(,100)

           

          You may be already doing this and are looking for a way to do it in a single calculation.  If so, I don't think V8 will help anymore with that.  No function that converts a number to a string or a date that I know of (we just got V8).

          • Converting  a numeric to a string
            Grant Perkins

            Hi Ron,

             

            The answer(s) may depend on whether your database source is a direct read from the database or perhaps is a delimited text file. I assume that are reading the data in directly rather than via an intermediate 'report' file.

             

            If you are using a delimited text file Version 8 offers the ability to specify the field type on 'read' through the model definition. It will also obey the date rules for the 'Input Options' which previously applied only to reports.

             

            Or you can use a calculated field.

             

            If you are reading directly from the database - ODBC, Access, Excel, etc., you

            can only use a calculated field solution as far as I know.

             

             

            The STR function will convert a numeric string to character. CTOD will convert a valid character string to a date format.

             

            So

             

            CTOD(STR())

             

            as the formula in a calculated field, should give you what you want - I think. (Remember to check your Input Options ...)

             

            You could include DAY(), MONTH() and YEAR() in the formula if you need to get these values individually.

             

            I hope this helps. Let us know if it does not.

             

            Grant

             

             

            Originally posted by ron cousino:

            Dates are stored as numerics in our database, i.e CCYYMMDD.  Since it is not a date field I need to be able to split the Year, month, and day.  Any suggestions on how to convert.  Currently using Monarch Pro 7.2.  Any CSTR functions in 8.0 like Crystal provides? /b[/quote]

            • Converting  a numeric to a string
              ron cousino

              Thanks to you all.  The Str function and the CtoD seem to work well with our database. The double commas in the function were causing me problems until I omitted the last argument.  This will go into my library of calculated fields since it will be used fairly often.